Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

piotrlipski
New Contributor II

WTD, MTD, YTD and running totals

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.

1.png

  • 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...

2.png

Last version of my WTD expression looks like below :

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

7 Replies

Re: WTD, MTD, YTD and running totals

You want this?

Capture.PNG

piotrlipski
New Contributor II

Re: WTD, MTD, YTD and running totals

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 ??

1.png

Re: WTD, MTD, YTD and running totals

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?

piotrlipski
New Contributor II

Re: WTD, MTD, YTD and running totals

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.

 

DateSalesWTD
1/1/201729352935
1/2/2017711510050
1/3/2017628816338
1/4/2017926325601
1/5/2017987035471
1/6/2017526940740
1/7/2017679847538
1/8/201772237223
1/9/2017585513078
1/10/2017742020498
1/11/2017812828626
1/12/2017612534751
1/13/2017917343924
1/14/2017461848542
1/15/201794799479
1/16/2017967419153
1/17/2017227421427
1/18/2017186223289
1/19/2017668429973
1/20/2017953039503
1/21/2017395143454

Re: WTD, MTD, YTD and running totals

Check attached....

Created two new fields in the script

piotrlipski
New Contributor II

Re: WTD, MTD, YTD and running totals

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 :/

Re: WTD, MTD, YTD and running totals

Should look better now

Community Browser