There may be instances where you’d like to calculate the cumulative sum in a chart in Qlik Sense. For example, you may want to look at sales values cumulatively summed in a bar chart for selected invoice months. On the other hand, maybe you want to accumulate subscription dollars over time to see how individual companies are growing within your ecosystem.
Cumulative sums or accumulation is possible in Qlik Sense; however, it may be challenging for the novice user to write the expressions to accomplish this. Formerly, in QlikView, you could select the ‘Full accumulation’ setting on the Expressions tab in the chart properties. However, there’s no such functionality in Qlik Sense unless you are using version Qlik Sense November 2019 and above. If you are using any version prior to the Qlik Sense November 2019 release, there is no native option for accumulated expressions in the user interface. As a result, you will need to create these expressions manually.
In that case, you are probably wondering how we can accomplish creating the accumulation or cumulative sum expressions in Qlik Sense… Without delay, let’s take a look.
Leveraging RangeSum() and Above() Functions for Accumulation Expression Writing in Qlik Sense
In Qlik Sense, we can use the RangeSum() function in combination with the Above() function to accumulate the values needed.
RangeSum() Function
RangeSum() returns the sum of a range of values. All non-numeric values are treated as 0.
Syntax: RangeSum(first_expr[, Expression])
| first_expr | The expression or field containing the data to be measured. |
| Expression | Optional expressions or fields containing the range of data you wish to measure. |
Examples:
| 1 | RangeSum(1,2,4) Returns 7 |
| 2 | RangeSum(5,’abc’) Returns 5 |
Above() Function
Above() evaluates an expression at a row above the current row within a column segment in a table. The calculated row depends on the value of offset, if present, the default being the row directly above. For charts other than tables, Above() evaluations for the row above the current row in the chart’s straight table equivalent.
Syntax: Above([TOTAL] expr [, offset, [, count]])
| expr | The expression or field containing the data to be measured. |
| offset | Specifiying an offset, greater than 0, moves the evaluation of the expression n rows further up from the current row. Specifying an offset of 0 will evaluate the expression on the current row. Specifying a negative offset number makes the Above() function work like the Below() function with the corresponding positive offset number. |
| count | Specifying a count greater than 1, returns a range of count values, one for each count table rows counting upwards from the original cell. In this form, the function can be used as an argument to any of the special range functions. |
| TOTAL | If the table is one-dimensional or if the qualifier TOTAL is used as argument, the current column segment is always equal to the entire column. |
Examples:
| 1 | Above(sum(Sales)) Returns the sum of Sales, but for the row above. |
| 2 | Above(sum(Sales),0,12) Returns 12 rows – the value for current row and the 11 rows above (specified by the 0 offset and 12 count). |
| 3 | Above(sum(Sales),1,3) Returns 3 rows – the three rows immediately above the current row (specified by the 1 offset and 3 count). |
Creating the Cumulative Sum Expression in Qlik Sense
Finally, with a better understanding of the RangeSum() and Above() functions, we can create our expression using a combination of the two functions to find the cumulative sum of Sales in Qlik Sense.
RangeSum(Above(sum(Sales),0,RowNo()))
In other words, this expression returns the cumulative values for sum(Sales) from the current row (specified with an offset of 0) and accumulates all preceding rows (specified with a count of RowNo()).
To further demonstrate, here’s the expression used in Expression Editor of Bar Chart:
The output:
Conclusion
To summarize, if you’re using any version of Qlik Sense prior to the November 2019 release, you can use the expression above to perform accumulation or cumulatively sum values. Additionally, with the detailed explanations of the Rangesum() and Above() functions, you can modify the expression to fit the precise needs of you and your business.
Learn more.
https://www.johndaniel.com/blog/
https://community.qlik.com/
https://help.qlik.com/
