Rolling sum using current dimensional date on table as a reference.

[marcoyukon]

Refer to data below. I need to create the following column "Sales Last 2-Months" on a table. I don't want to use RangeSum(below(Sales,0,2)) as it doesn't return the desired results when SORTING the YearMonth column.

I've tried a different approach below, but this expression returns  only the current month's total and not the desired current + previous sales total.

What am I missing on the expression below to make this rolling 2mt sum work ?

Sales Last To 2 Mt=

SUM( {$<YearMonth={">=$(=MONTHSTART(YearMonth),-2)<=$(=MONTHSTART(YearMonth),0)" }> }TotalAmount)

YearMonth	Sales	Sales Last 2-Months (?)
2017-12	200,000	-
2018-01	300,000	500,000
2018-02	400,000	700,000
2018-03	500,000	900,000
2018-04	600,000	1,100,000
2018-05	700,000	1,300,000
2018-06	800,000	1,500,000
2018-07	900,000	1,700,000
2018-08	900,050	1,800,050
2018-09	900,150	1,800,200
2018-10	900,250	1,800,400
2018-11	900,350	1,800,600
2018-12	900,450	1,800,800

Sample Data:

Data:
LOAD
Dual(Year(YearMonth)&'-'&Num(Month(YearMonth),'00'), monthstart(YearMonth)) AS YearMonth,
TotalAmount
INLINE [
YearMonth, Sales
'12/01/2017',200000
'01/01/2018',300000
'02/01/2018',400000
'03/01/2018',500000
'04/01/2018',600000
'05/01/2018',700000
'06/01/2018',800000
'07/01/2018',900000
'08/01/2018',900050
'09/01/2018',900150
'10/01/2018',900250
'11/01/2018',900350
'12/01/2018',900450
];

[marcoyukon]

I posted this a few weeks back and wanted to check if anyone has found a similar solution without using rangesum() or rangeavg(). Any feedback will be appreciated.

[Channa]

Try this app

expression:Sum(PSales)+SUm(TotalAmount)

LOAD
Dual(Year(YearMonth)&'-'&Num(Month(YearMonth),'00'), monthstart(YearMonth)) AS YearMonth,
Sales as TotalAmount,
Previous(Sales)as PSales;
Data:

load * INLINE [
YearMonth, Sales
'12/01/2017',200000
'01/01/2018',300000
'02/01/2018',400000
'03/01/2018',500000
'04/01/2018',600000
'05/01/2018',700000
'06/01/2018',800000
'07/01/2018',900000
'08/01/2018',900050
'09/01/2018',900150
'10/01/2018',900250
'11/01/2018',900350
'12/01/2018',900450
];

[nagaiank]

You may use Above function also.

[Vegar]

There something strange with your expression which is causing you trouble. 

SUM( {$<YearMonth={">=$(=MONTHSTART(YearMonth),-2)<=$(=MONTHSTART(YearMonth),0)" }> }TotalAmount)

You are using a 3 day interval when you want  months.

Adjust it to

SUM( {$<YearMonth={">=$(=MONTHSTART(YearMonth,-2)<=$(=MONTHSTART(YearMonth)" }> }TotalAmount)

[Vegar]

I don't think your expression will work as intended. You should know that all $-expansions are evaluated outside of your chart.
$(=MONTHSTART(YearMonth)) will return the same result on all rows in your table. And in this case it will only return a value if you have reduced you data down to one single available YearMonth.

Try putting your $-expression, =MONTHSTART(YearMonth), into a text box to validate a valid result.

[Vegar]

Hi again
The expression you where talking about in your initial post have been efficient way to calculate accumulating sums for me many times.

sum( aggr( rangesum( above( sum(Sales),0,2) ),YearMonth))

Is it possible to sort out the sorting issue you are experiencing in order to make it work for you?

I would recommend you to read @Gysbert_Wassenaar excellent posting from 6 years back, it describes some alternative approach to your problem. https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-oth...

[marcoyukon]

Yes this is the only way I got the calculation to work, well partially. How do you resolve the issue of when the report user sorts the date column in desc order  ?