Passing value to HTML table according to date

[Mohd faizal Adris]

I have an HTML table in the Text area which containsdate information thatwill change dynamically based on the fiscal week.

 

What I'm trying to do is to make the tablecontents change its values according tothe date value inside the table header respectively.

I had tried usingcalculated values but it not solve my problem. Any suggestions

[Shashank Gurnalkar]

You can use the following expression to get the header (dates for thisweek) as:

#10th May

DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",0-DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))

 

#11th May

DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",1-DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))

 

#12th May

DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",2-DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))

 

#13th May

DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",3-DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))

 

#And so on..Also, in the column values, you can use calculated values and use case statements to get the required values. For example, to get the Sum value for apple on 11th May, you can use the following expression (Assuming the column name is same in the data table):

Avg(Case

When [item]="Apple" and ([Date]=DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",1 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))) Then Count([item])

End)

[Mohd faizal Adris]

Hi Shashank,

 

I had tried to replicate your solution but the calculated values didn't return any result after the following expression being added. Please help to guide me.

 

case  

WHEN ([Remark]="Material Expire With In 30Days") And ([Expired Date]=DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",1 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))) THEN Count([Remark])

END 

 

 

 

[Shashank Gurnalkar]

Can you share your DXP file with embedded data

[Mohd faizal Adris]

I have prepared some mockup data in the DXP file to help visualize it. See attached.

[Mohd faizal Adris]

The DXP file with mockup data.

[Shashank Gurnalkar]

Looks like you forgot to embed the data, and it coming blank at my end.

[Mohd faizal Adris]

I have updated the DXP file and save it as stored data. Let me know if the data still missing. Thanks.

[Shashank Gurnalkar]

I have updated my answer and attached the DXP file. The expression needs to be aggregated, so  I put it in the Avg() function. I did this for Apple, and for remaining parts you can do it accordingly. 

[Mohd faizal Adris]

Thanks for your kind help! It's helped to solve my problem. Really appreciate!

[Mohd faizal Adris]

Seems like the date inside the table contents didn't automatically change the same as the date in the header. Any suggestion on how to make it 'dynamics' and tally