WTD, MTD, YTD and running totals

[Anonymous]

Hello,

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 :

=RangeSum(Above( sum( {<Date=>}Sales),0,7))

[sunny_talwar]

You want this?

[Anonymous]

Looks better but figures are  incorrect. If you don't select anything (it's also an option) we don't have "reset" after week or month.

I see change only with extra "*avg(1)" in expresion. Did you change something more ??

[sunny_talwar]

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?

[Anonymous]

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.

Date	Sales	WTD
1/1/2017	2935	2935
1/2/2017	7115	10050
1/3/2017	6288	16338
1/4/2017	9263	25601
1/5/2017	9870	35471
1/6/2017	5269	40740
1/7/2017	6798	47538
1/8/2017	7223	7223
1/9/2017	5855	13078
1/10/2017	7420	20498
1/11/2017	8128	28626
1/12/2017	6125	34751
1/13/2017	9173	43924
1/14/2017	4618	48542
1/15/2017	9479	9479
1/16/2017	9674	19153
1/17/2017	2274	21427
1/18/2017	1862	23289
1/19/2017	6684	29973
1/20/2017	9530	39503
1/21/2017	3951	43454

[sunny_talwar]

Check attached....

Created two new fields in the script

[Anonymous]

WTD looks fine but MTD not. On 1st of February we have something wrong.

What is worst for me I don't understand what this Sort1 and Sort2 doing for us 😕

[sunny_talwar]

Should look better now