Friday, December 2, 2022
HomeBusiness AnalyticsCumulative Sum in Qlik Sense

Cumulative Sum in Qlik Sense


There could also be situations the place you’d wish to calculate the cumulative sum in a chart in Qlik Sense. For instance, it’s possible you’ll wish to have a look at gross sales values cumulatively summed in a bar chart for chosen bill months. However, possibly you wish to accumulate subscription {dollars} over time to see how particular person corporations are rising inside your ecosystem.

Cumulative sums or accumulation is feasible in Qlik Sense; nonetheless, it might be difficult for the novice person to write down the expressions to perform this. Previously, in QlikView, you may choose the ‘Full accumulation’ setting on the Expressions tab within the chart properties. Nevertheless, there’s no such performance in Qlik Sense except you’re utilizing model Qlik Sense November 2019 and above. In case you are utilizing any model previous to the Qlik Sense November 2019 launch, there isn’t any native choice for collected expressions within the person interface. In consequence, you have to to create these expressions manually.

In that case, you’re most likely questioning how we are able to accomplish creating the buildup or cumulative sum expressions in Qlik Sense… Immediately, let’s have a look.

Leveraging RangeSum() and Above() Capabilities for Accumulation Expression Writing in Qlik Sense

In Qlik Sense, we are able to use the RangeSum() perform together with the Above() perform to build up the values wanted.

RangeSum() Operate

RangeSum() returns the sum of a variety of values. All non-numeric values are handled as 0.

Syntax: RangeSum(first_expr[, Expression])          

first_expr The expression or subject containing the info to be measured.
Expression Elective expressions or fields containing the vary of information you want to measure.

Examples:          

1 RangeSum(1,2,4)
Returns 7
2 RangeSum(5,’abc’)
Returns 5

Above() Operate

Above() evaluates an expression at a row above the present row inside a column section in a desk. The calculated row relies on the worth of offset, if current, the default being the row straight above. For charts aside from tables, Above() evaluations for the row above the present row within the chart’s straight desk equal.

Syntax: Above([TOTAL] expr [, offset, [, count]])

expr The expression or subject containing the info to be measured.
offset Specifiying an offset, larger than 0, strikes the analysis of the expression n rows additional up from the present row.
Specifying an offset of 0 will consider the expression on the present row.
Specifying a detrimental offset quantity makes the Above() perform work just like the Beneath() perform with the corresponding constructive offset quantity.
rely Specifying a rely larger than 1, returns a variety of rely values, one for every rely desk rows counting upwards from the unique cell.
On this kind, the perform can be utilized as an argument to any of the particular vary features.
TOTAL If the desk is one-dimensional or if the qualifier TOTAL is used as argument, the present column section is at all times equal to your complete column.

Examples:          

1 Above(sum(Gross sales))
Returns the sum of Gross sales, however for the row above.
2 Above(sum(Gross sales),0,12)
Returns 12 rows – the worth for present row and the 11 rows above (specified by the 0 offset and 12 rely).
3 Above(sum(Gross sales),1,3)
Returns 3 rows – the three rows instantly above the present row (specified by the 1 offset and three rely).

Creating the Cumulative Sum Expression in Qlik Sense

Lastly, with a greater understanding of the RangeSum() and Above() features, we are able to create our expression utilizing a mix of the 2 features to seek out the cumulative sum of Gross sales in Qlik Sense.

RangeSum(Above(sum(Gross sales),0,RowNo()))

In different phrases, this expression returns the cumulative values for sum(Gross sales) from the present row (specified with an offset of 0) and accumulates all previous rows (specified with a rely of RowNo()).

To additional reveal, right here’s the expression utilized in Expression Editor of Bar Chart:

Cumulative Sum Accumulation Qlik Expression

The output:

Conclusion

To summarize, if you happen to’re utilizing any model of Qlik Sense previous to the November 2019 launch, you should use the expression above to carry out accumulation or cumulatively sum values. Moreover, with the detailed explanations of the Rangesum() and Above() features, you’ll be able to modify the expression to suit the exact wants of you and your online business.

Study extra.
https://www.johndaniel.com/weblog/
https://group.qlik.com/
https://assist.qlik.com/

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments