The charts in Qlik Sense don't provide the user with the in-built functionality to calculate the cumulative totals, as is the case with QlikView. In order to achieve the cumulative totals in a trendline chart, we make use of the RangeSum() function.
Load the following script that gives information of monthly sales figures for 2 years:
Sales: LOAD Month(Date#(Month,'MMM')) as Month, Year, Sales INLINE [ Month,Year,Sales Jan,2014,1000 Feb,2014,1520 Mar,2014,1600 Apr,2014,3000 May,2014,2500 Jun,2014,4500 Jul,2014,6000 Aug,2014,6500 Sep,2014,7800 Oct,2014,6800 Nov,2014,3000 Dec,2014,2500 Jan,2015,750 Feb,2015,1200 Mar,2015,800 Apr,2015,600 May,2015,2100 Jun,2015,3500 Jul,2015,4700 ];
- Click on App overview under the Navigation dropdown and create a new sheet.
- Drag across the
object from the Assets panel on the sheet and name it Cumulative Sales. - Add Year and Month as the dimensions.
- Next, add the following measure and label it
Cumulative Sales:RANGESUM(ABOVE(TOTAL Sum(Sales),0, ROWNO(TOTAL)))
-
Save the application and click on
. - The final trendline chart should look like the following:
There are three arguments defined in the syntax used for the Above() function:
Expression = Sum(Sales)Offset = '0'Since this is zero, the function evaluates the expression on the current row.
Count = RowNo(Total)The third argument tells the
Above()function to evaluate the expression over a range of values. In this case, because we are specifying a total inside theRowno()function, the result would be the number of the row the user is currently on.
The Above() function will return a range of values. Hence we will use the RangeSum() function to sum up all the values.