Show Value of every 7th day (Rolling 7th days)

[johngouws]

Good morning. 

I am looking for assistance in showing the Value for every 7th day in a line chart. The reason for this is that the data I am given access to only has daily accumulative values. For example, today is Sunday. I would like to see the values of all  Sundays. Tomorrow I need to show the values for all Mondays, and so on. In the snippet it should be for the yellow highlighted days. 

My first attempts are to use RangeSum(Above(Max({<CostCentre={">5"}>} Value),0,7)  or RangeMax(Above(Avg({<CostCentre={">5"}>} Value),0,7), but they do not return the correct results. The reason why Max does not work is that, although the data is accumulated, because of credits and journals, the last value, for Sunday, may not be the highest value. 

Any assistance will be appreciated - thank you in advance. 

[Vegar · Accepted solution]

If the data already is accumulated then you won't need to accumulate it again using Rangesum)(above(...)). Or have I missunderstood your issue?

If this is a correct understanding then you "just" need to figure out how to which week day it is today, because the following expression will give you the accumulated values for Sundays.

Sum({<Weekday={'Sunday'}, CostCentre={">5"}>} Value)

If you have a calculated variable you could solve it using it. Like this. 

/*ensure correct definitions of your System variable DayNames */

Set DayNames= 'Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

/*define a variable as today's weekday*/

Let vWeekday=WeekDay(today( ) );

In your table use this expression (You might want to change the sum() to max(), avg() or only() to suit your needs) :

Sum({<Weekday={'$(vWeekday)'}, CostCentre={">5"}>} Value)

[johngouws]

Hi Vegar, 

Thanks for your reply. Your suggestion works spot on. Using Only, Only({<Weekday={'$(vWeekday)'}, CostCentre={">5"}>} Value)  gives the result I need. 

Thank you very mich. 

[Vegar]

That's great!

I'm glad I could be of help.