Qlik Community

Qlik Design Blog

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

etk
Contributor

Accumulative Sums

We all use QlikCommunity when we need an answer for a QlikView question. We post a question and get an answer, usually in minutes, leveraging the expertise of 91,000+ QlikView users! QlikCommunity is the most-visited and active user community in the BI industry.

I also use QlikCommunity. And sometimes not to get an answer for a question but to think about new ways of doing things by reading the answers on the existing posts. So I have been curious about the answers on calculating the accumulative sums in QlikView and did a search on QlikCommunity. There are many answers provided as expected. Here is one way of achieving it by using rangesum() and aggr() functions.

It is a very common requirement to display the accumulative sum of a chart metric. The easiest way of doing this is to use the accumulate setting under the expressions tab of chart property.

Chart accumulation (2).jpg

Figure 1. Accumulate option on chart property

With this setting, the values of the selected metric will be accumulated. But sometimes, the requirement is to have the first data point accumulated as well. For example, to show the accumulated sales for 12 months where the first month displayed should be the sum of the previous 12 months and the following months should accumulate on top of this value by adding one month at a time. One way of achieving this is to use the rangesum() and the aggr() functions together.

Let us first look at Rangesum(). It is a range function that returns the sum of a range of 1 to N arguments Together with the above() function, you can calculate the sum of the last 12 months;

rangesum( above( sum(Sales),0,12) )

This expression will return the sum of sales evaluated for each month as they appear on the 12 rows above the current row.

The next step is to combine RangeSum() with  the aggr() function to aggregate the sum of sales for the 12 months at the month level.

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))

Using the Aggr() function, it is possible to calculate the accumulated sum for any other dimensions in addition to  the time dimension. For example, the 12 months accumulated sum of sales by store can be calculated as;

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month, Store))

Optionally you could also clear any selections on the month field as the calculation should reflect the accumulated sales for the previous 12 months, regardless of selections. This is achieved by using the set analysis “{$<Month>}”.

sum( aggr( rangesum( above( sum( {$<Month>}  Sales),0,12) ),Month))

One important thing to notice with this solution is the sort order inside the aggr() function. If the sort order is not correct, the aggregated sum of the 12 months would not be correct. QlikView always sorts the groupings (in this case the groups defined by the second parameter of the aggr() function) according to the load order. If the data is sorted by date during the load, QlikView will use this order and the aggr() function will show the correct totals for the desired time frame.

In summary, the accumulation option on the expression tab is the simplest way of achieving accumulations, but the use of rangesum() and aggr() functions are alternative ways of accumulating the data in more complex situations.

32 Comments

Nice one Elif!

One blog post for several dozens of replies in different places under different topics and subjects.

This issue is quite commonly asked, and thinking of rollings (months, years, periods, etc.), the value of your post becomes clear.

Thanks!

0 Likes
2,425 Views
Not applicable

Excellent post Elif,

But got one question what if, I want to see the amounts of the 12 month sum, but not by rows but by columns

DATA.bmp

As you can see, i have 6 products here, and those columns that each one shows, the sum of that month plus the 11 previous ones.

Any help in this case? any ideas?

Thank you very much.

0 Likes
2,425 Views
orabrabbo
New Contributor II

In the expression used before:

rangesum( before( sum(Sales),0,12))

2,425 Views
Partner
Partner

Very nice!

2,425 Views
carbal1952
Contributor II

Undoubtedly magnificent !

CB.

2,425 Views
Not applicable

Hey guys, 

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....

0 Likes
2,425 Views
orabrabbo
New Contributor II

If(ColumnNo()=21,RangeCount(After(Count([Fact]),0,Max(total [Calculated Dimension])-20)),Count([Fact]))

0 Likes
2,425 Views
orabrabbo
New Contributor II

In the expression above using the combination "ColumnNo() / after" if the value is calculated based on the values of the size of the graph as they appear in the next column, otherwise use "RowNo() / below" if the value is compared based on the values of the size of the graph as they appear in the next line.

0 Likes
2,425 Views
Not applicable

I have used this calculated dimension to calculate the frequency

if(isnull(ENTITY_DISPLAY_NAME_VOD_C),aggr(if(count(v_ID_no_Parent)<>0,Count(v_id_no_Parent),[$(vGrp)],PRIMARY_ACCOUNT_C),aggr(if(Count(v_ID_no_Parent)<>0,count(v_ID_no_Parent)),[$(vGrp)],ENTITY_DISPLAY_NAME_VOD_C))

vgrp is for cyclic group ....v_id_no_Parent indicates calls ...rest is to calculate the right HCP count ....we are basically calculating frequencies of calls per territory..

Please help me with this...I need the solution asap...Can you people just modify the code according to the requirement...i tried...its not working...

0 Likes
2,425 Views
orabrabbo
New Contributor II

You kindly send me a sample *.qvw file to make the changes. How well do you understand the problem ? Thanks you. AB

0 Likes
2,425 Views
Not applicable

Thank you for the great post!

I have a bubble graph with three expressions and one dimension:

  • X-axis: total price -> sum(Price)
  • Y-axis: price per unit -> sum(Price)/sum(Quantity)
  • Bubble: total Quantity -> sum(Quantity)
  • Dimension: Category

Now I want to animate this bubble graph based on time("period").I want to use the accumulated Price and Quantity to calculate the price per unit. However, the "Full Accumulation" in the Expressions tab adds up the price per unit for each period, which is not what I want. I came across your post and tried the following expression for the Y-axis:

  • sum( aggr( rangesum( above( sum(price),0,3) ),Period, Category)) / sum( aggr( rangesum( above( sum(Quantity),0,3) ),Period, Category))

But this gives me the price per unit based on each period's data instead of cumulative data. I have attached a qlikview example for reference (in this link). I would like the animated graph to resemble the total price graph on top when the animation reached period 3 (currently, the price per unit is not showing correctly).

Any help would be greatly appreciated!

0 Likes
2,425 Views
orabrabbo
New Contributor II

You will find the solution at the following link...http://community.qlik.com/message/482450#482450

0 Likes
2,425 Views
punitpopli
Contributor III

Hi Elif Tutuk :

Thanks for this superb post.

Is there a way where we can found the Avg of the rolling months, as the above post is based on Sum.

I have tried using Avg function but it didn;t work, so just thought to reply to this post

Thanks in Advance

-Punit

0 Likes
2,425 Views
orabrabbo
New Contributor II

Hi,

give me, please, a concrete example of what you want to do about the data source and data target.

Thank's

0 Likes
2,425 Views
punitpopli
Contributor III

Hi antonino barbaro

I was actually looking for rolling average for last 4 week (in our case) and so on, in the same way as rolling sum mentioned above for rangesum. I had achieve it by using RangeAvg

e.g. : rangeavg( below( sum(Sales),0,4) )


Thanks

Punit

2,425 Views
orabrabbo
New Contributor II

You can also use the following expression:

rangeavg( above( sum(sale),0,4) )

2,425 Views
Not applicable

Hi Elif:

Great post!!

I have a question when I try to calculating the Rolling 12 month leavers.

I have records for each leavers, there are leave dates and leave types etc.

When I use your method, I found out that it works fine as long as there is data for the particular month.

I mean the expression works for rolling 12 month total leavers because there are leavers for each month.

But when I calculate the rolling 12 month leavers for a particular leave type, if there is no leavers for the type in a month, the result would be 0 for the month instead of total for the last 12 month.

I wish you understand my issue and provide me some guidance about the solution for this issue.

Thank you very much.

PC

0 Likes
2,425 Views
punitpopli
Contributor III

Hi etk‌, Orabrabbo

I am using the below formula in one of the line chart for 53 weeks to calculate rolling 4 week average .

IF(Count({$<WeekId={'>$(=max({1}WeekId)(53))'}>}sales)>0, RangeAvg(Above(sum(sales),0,4)), null())


But in my case I have a cyclic group icon on z-axis and in that case this above exp is not returning the correct result.


Is their a way to include cyclic group in the expression?


Thanks,

Punit

0 Likes
2,425 Views
Not applicable

Hi Punit:

Cyclic groups can be used in calculations by using square bracket and GetCurrentField.

For example:

[$(=GetCurrentField(YourCyclicGroup))]

Consider the whole thing as a field with square bracket when using in calculations should be OK.

Thank you.

PC

2,425 Views
Partner
Partner

Hi,

Thank you very much for this post .. It really helps !!

The  expression sum( aggr( rangesum( above( sum( {$<Month>}  Sales),0,12) ),Month)) is working fine only if the month is in sorted order .. i tried to load teh data and then sort in qlikview script. But it doesnt work fine .. Anyone knows how to handle this scenario and make it to work if the data is alreay not in sorted order ?


your help is very much appreciated ..


Thanks,

Nirmal.

0 Likes
2,425 Views
Partner
Partner

Very nice but there is a flaw with this!

Aggr builds a temporary table that works the same way as a straight table.

If there are no sales in a month then the Above inside Aggr will go back one more month.

It's more obvious with rolling 3 months, the expression;

sum( aggr( rangesum( above( sum( {$<Month>}  Sales),0,3) ),Month))

Say we are closed for July thus selling $0 or by user selection there is no data in July.
Then for August it would aggregate aug+jun+may instead of aug+(jul)+jun,

September would add together sep+aug+jun instead of sep+aug+(jul).

We need the expression to evaluate a value each period regardless if there are data in that period or not.

I have managed though to overcome this problem using the following expression.

It also does away with using Aggr but can be a little hard to understand.

Include inside Above is the strange Sum({1}0) which ensures there is a calculated
value each month even for July in my example above.

But since the sums inside the RangeSum calculates for all months regardless

of selection we need to limit the result to only the selected months.

Here the Count(...) helps by calculating 1 in selected months and 0 for the other months.

Tried using Sum() or Only() but that does not work.

Using P(Month) in the set-analysis won't work since that would again obscure July in my example.

Values would be correct but July omitted even though it should add (jul)+jun+may

Count({1<Month={">=$(=Min(Month))<=$(=Max(Month))"}>} DISTINCT 1) *
RangeSum(Above(Sum({1}0) + Sum({$<Month>} Sales), 0, 3))

2,425 Views
Arjunarao
Honored Contributor II

Good post. Thank you for sharing...

0 Likes
2,425 Views
Not applicable

No of the aforementioned worked for me, anyway thanks for posting!

0 Likes
2,425 Views
pascaldeclarens
New Contributor

Hello,

many thanks for your help.

I am afraid that this did not solve my problem...

I used the sum( aggr( rangesum( above ( sum {$<Month>}[Total HT Net],0,12) ),Month)) (with or without the month check)

note: in my case the field name I want to cummulate is called 'Total HT Net'

And I get a line with value= 0

I enclose below the chart, below is the linechart with montly sales for years 2016 and 2016, at the top is the line chart using the rangesum function

Capture d’écran 2016-09-18 à 09.37.25.png

Where am I wrong?

thanks

0 Likes
2,425 Views
Employee
Employee

pascaldeclarens‌ - a late reply, but can you check your measure? it looks like it is missing some ( and ) around the SUM

You posted:

sum( aggr( rangesum( above ( sum {$<Month>}[Total HT Net],0,12) ),Month))


Try this?


sum(

     aggr(

          rangesum(

               above (

                    sum ( {$<Month>} [Total HT Net] )

               ,0,12)

           )

     ,Month)

)

0 Likes
2,425 Views
timmarsh
New Contributor III

How would you calculate an accumulation to the beginning of time?

Imagine I am plotting a % of something across time.

Each month I have a number of something - it changes each month. Emergency Department presentations.

I want to compare it (denominator) to something else - number of patients enrolled in a program - which I want to accumulate across, but I want the accumulation back to me first data point?

That means, I want to compare that month's ED presentations to an accumulated (to that point) sum of patient records.

Starting from here: =sum(Unique_ED_Presentations)/sum(Patient_Record_Counter).

Can't paste data, health data.

Thanks for any help!

Tim

0 Likes
2,425 Views
gr_eldred
Contributor II

I have 52 weeks of the year 2017 in a simple table

I want to add 6 weeks sales.

111.png

I'm just bringing the last 6 weeks of information, what I need is that in each week I get the total of the sum of 6 weeks ago.

sum({$<Status-={'C'}, SemamaAñoApple_Numerado = {">=$(=Max(SemamaAñoApple_Numerado)-6)<=$(=Max(SemamaAñoApple_Numerado))" }, Año={$(=only(Año))}>} Cantidad)

Example:

Week 6 must bring the sum of 6 weeks back (week1 + week2 + week3 + week4 + week5 + week6)

Week 7 has to show the sum of 6 weeks ago (week2 + week3 + week4 + week5 + week6 + week7)

0 Likes
2,425 Views
Partner
Partner

Well you can't do it with set analysis.
An important insight about set analysis is that it is applied to your data set before the calculations in your table.

You have the original data set in the application filtered through the users selection.
On that the set analysis is applied altering the filtering and producing a new data set.
This data set is then used in the calculation.

What this means is the set analysis does not take into account your dimensions in the table because the set analysis is applied once for the whole column (or row since you have turned it around).

After the set analysis is applied, the expression is calculated for each cell using the resulting data set.

So what your set analysis does above (with my limited Spanish) is to take the maximum WeekYear (SemamaAñoApple_Numerado) and filter your data to include the maximum WeekYear and six week before.

You need instead to use expressions that reference the six weeks before in your table and there is where functions RangeSum and Above comes in.

Above lets you calculate the expression and get the result if you had been in previous cell (or more cells back depending on your parameters).

You can then combine results from Sum in the current cell with Sum if you had been in those previous cells to get the result you are looking for.

Start easy with just using Above once and see that you get the Sum for previous week.
Then expand on that until you have what you are looking for.

Forget about set analysis and trying to filter what each individual cell is calculating because that's not the way it works.

You might need set analysis to expand the time frame to include six weeks before your users selections to get the first cells correct.
Otherwise you will get a ramping effect for the first six cells.

0 Likes
2,425 Views
davidoxi
New Contributor III

Hi ,

I have the following calculation formula :

sum(Aggr(count(if(match([Type de commande], 'URGENT','GROS', 'PVI'), [N° ligne])),[N° donneur d'ordre]))/Aggr(sum(Aggr(count([N° ligne]),[N° de commande])),[N° donneur d'ordre]) ;

I want to chart Chart line by Last 6 Months

2,425 Views
gr_eldred
Contributor II

Thank you

0 Likes
2,425 Views