Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
marcoyukon
Creator
Creator

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

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)

YearMonthSalesSales Last 2-Months (?)
2017-12200,000-
2018-01300,000500,000
2018-02400,000700,000
2018-03500,000900,000
2018-04600,0001,100,000
2018-05700,0001,300,000
2018-06800,0001,500,000
2018-07900,0001,700,000
2018-08900,0501,800,050
2018-09900,1501,800,200
2018-10900,2501,800,400
2018-11900,3501,800,600
2018-12900,4501,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
];

 

7 Replies
marcoyukon
Creator
Creator
Author

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
Specialist III
Specialist III

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
];

Channa
nagaiank
Specialist III
Specialist III

You may use Above function also.

 

Vegar
Partner
Partner

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Vegar
Partner
Partner

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Vegar
Partner
Partner

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...
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
marcoyukon
Creator
Creator
Author

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  ?