Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

 

When building analytical applications you very often encounter cases where you want to accumulate numbers inside a chart. It could be that you want to calculate a Year-to-date number, or a rolling 6 month average, or a moving annual total.

When creating an accumulation, there are two fundamental challenges. First, there is the question on how to treat dimensional values with no data. The tables below illustrate the problem:

Missing month.png

In this example, there is no data for the month of March. The default behaviour is that this row then is omitted, see left table. This leads to an incorrect accumulation – the rolling 3 total for April includes January, which it shouldn’t. The table to the right however treats missing months correctly.

Secondly, there is the question on how to treat the first dimension when there are several dimensions. The tables below illustrate the problem:

First dimension.png

In the left table, you want the accumulation to re-start for each new product. You do not want the accumulation to continue from the previous product. Note that for ‘2014 Jan’ the accumulation is reset.

But in the right table, it is the other way around: A moving annual total should span over two calendar years, so you want the accumulation to continue over into a new value of the first dimension.

In QlikView and Qlik Sense there are several ways to create accumulations, each with its own pros and cons.

1. Using the control in QlikView charts

In a QlikView chart, you can easily create an accumulation just by selecting the right chart setting:

Chart UI accumulation.png

This method will however fail both the above challenges. Also, an accumulation can only be made over the first dimension, and the accumulation will always be reset for a new value in the second dimension.

Further, you cannot use it to calculate moving averages. It only works for calculations of sums.

2. Using the Above() function

If you use the Above() function in a chart table like

     RangeSum(Above(total Sum(Amount) , 0, 12))

or in an Aggr() table like

     Only(Aggr(RangeSum(Above(total Sum({Amount),0,12)), YearMonth))

you will get a 12-month moving total. It will pass the “multiple dimensions” challenge: By using – or not using – the total specifier in the Above() function you can decide which behaviour you want.

But it will fail the “no data” challenge. There is a however a way to make an accumulation in an Aggr() table work also for this problem: Trick the QIX engine to show all rows by adding a zero term for all dimensional values:

     Only(Aggr(RangeSum(Above(totalSum(Amount) + Sum({1} 0),0,12)), YearMonth))

and enable the display of zero values. Then you will get the right numbers also when some dimensional values are excluded.

3. Using an As-Of table in the data model

The As-Of table, is an excellent solution that will pass both challenges. Here you must set the condition using Set Analysis:

     Sum({$<MonthDiff={"<12"}>} Amount)

However, it has one drawback: When a user clicks in the chart, a selection will be made in the AsOfMonth. But you don’t want the user to make selections here: You want month selections to be made in the real month field. So I usually make my charts read-only if they use the AsOfMonth as dimension.

With this, I hope that you understand accumulations better.

HIC

 

Further reading related to this topic:

The Above Function

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

7 Comments
balrajahlawat
Esteemed Contributor

Good one HIC,

If I am not wrong, I guess at backend/script level as well, we can do this accumulation.

Using combination of Numsum() & Peek().

Really helpful when it comes to accumulation at UI/Front end

0 Likes
2,060 Views
tdz
New Contributor III

Great explanation, thank you very much!

0 Likes
2,060 Views
Partner
Partner

Now then case two looks somewhat familiar.

0 Likes
2,060 Views
Luminary
Luminary

Thank you HIC.

Balraj - How can you do the backend/script accumulation when users can select any combination? Am I missing something here?

Cheers,

DV

2,060 Views

Balraj

Yes, you can make the accumulation in the script as you indicate. If so, you should take all dimensions into account, i.e. all combinations of month, customer, product, warehouse, shipper, etc must be calculated. Otherwise you will have accumulations that are static; that will not change as a user makes a selection. This means that the app could become much larger than necessary.


HIC

2,060 Views
balrajahlawat
Esteemed Contributor

Okay,

So, when it comes to accumulation, best practice will be implementing it at front end instead of script level..

Thanks for your suggestion HIC!!

0 Likes
2,060 Views
robert99
Valued Contributor III

Thanks. This post has been very helpful

I had to modify it to allow a line chart (by quarter) drill down (Country -> Project as follows

if (GetSelectedCount (Country) = 1 ,

only (aggr( rangesum( above( sum(Beneficiaries)+ sum ({1}0)  ,0,1200) ), Project, Quarter )),

ONLY (aggr( rangesum( above( sum(Beneficiaries)+ sum ({1}0)  ,0,1200) ), Country , Quarter  )))

As long as "Order By" in the script works (it often doesn't so i try to avoid using it) this seems to do the trick.

0 Likes
2,060 Views