I would like to prepare very simple report (I thought) but I have some problems with running totals.
I would like to have report with 4 expressions: Current Sales, WTD Sales, MTD Sales and YTD Sales.
Problem which I have is that user need to have possibility to select eg. only one day or random period.
I found two ways for that but both are not perfect.
First is when I have in dimension all measures like Year, Month, Week. In such case figures looks fine but I have 5 dimension instead of 2. In final version I really would like to keep it simple with only Store and Date.
Second option is to use set analysis and TOTAL but in such case I don't have accumulative WTD but only final totals for week.
I tried to find solution with first option but when I keep in dimension only Store and Date I have such things...
Last version of my WTD expression looks like below :
Yes, multiplying with Avg(1) was the only change. With regards to incorrect numbers, are you saying that even without selections, the numbers are not right? or do you want to see different numbers after the selection is made?
When we don't select anything we have list of all days. Expression with WTD should show cumulative WTD so Monday = Monday, Tuesday = Monday + Tuesday, etc. but on next Monday everything should be reseted and start again Monday = only Monday.
The same for MTD we have day by day sum and after 1st everything start again.
In you example we have continuity so figures are correct only for first week, first month, etc.