topic Re: Cumulative sum in QlikView

Cumulative sum

Sat, 06 Apr 2013 12:00:56 GMT

Hello,

I have some difficulties for calculating the cumulative sum in an expression to be used in a bar chart. I need the same a result as the "full accumulative" feature in the bar chart cumulative option but in the expression.

I need it in the expression because it must be devided by another field which must not be accumulative.

Here is my scenario. I have the following chart :

It is simply obtained with a Sum(Revenue) and using the full accumulative feature. The revenue of march 2011 for the blue cohort is the accumulative revenue (jan + feb + march).

The problem is that I need the devide or substract this accumulative revenue by the cohort's Cost of Acquisition wich must not be accumulative.

For example, the CAC of the blue cohort is 2 millions, I should have :

in January 2 millions - January revenue

in Fev 2 millions - (January + Feb revenue)

In March 2 millions - (January + Feb + March revenue)

And so on

Just one part of the expression (the revenue) must be accumulative.

Any idea how to obtain that ?

Re: Cumulative sum

IAMDV — Sat, 06 Apr 2013 23:53:42 GMT

Hi,

It will great if you can post your sample QVW. However, you should be looking for RangeSum() & Above() functions to calculate the accumulative sums.

I hope this helps!

Cheers,

www.QlikShare.com

Re: Cumulative sum

swuehl — Sun, 07 Apr 2013 23:35:23 GMT

You are using multiple dimensions ( I assume they are named liked MONTH and COHORTE), so you might need to additionally use advanced aggregation to get your column segments for the accumulation using above() right, maybe along these lines:

=aggr( rangesum(above( sum(REVENUE), 0, rowno() )), COHORTE, MONTH)

edit: One more thing, if you are using the advanced aggregation (aggr() function), you'll need to ensure, that MONTH field values load order is chronological ascending, e.g. by loading a master calendar first. The aggr() function's dimension values will always be ordered in load order (not regarding any order you may define for the chart).

Re: Cumulative sum

jagan — Mon, 08 Apr 2013 11:05:48 GMT

Hi,

PFA file, hope it helps you.

Regards,

Jagan.

Re: Cumulative sum

Mon, 08 Apr 2013 12:07:32 GMT

Hi,

I'm trying with RangeSum(Above(Sum(REVENUE),0,RowNo())). The problem is that this fonction depend on the sort order, we can figure it in these two tables :

The first one is obtained by changing the sort order of the columns and the result is OK.

The second is the original table obtained from the chart. The problem is that I cannot change the sort order of columns in the chart. So I need an expression where the rowno is obtained from the following sort order : COHORT, MONTH

Re: Cumulative sum

Tue, 09 Apr 2013 07:55:03 GMT

Thanks a lot swuehl, that worked when changing load order. Unfortunately I can't choose your answer as best answer because I can't loggin with the previous account !

Re: Cumulative sum

Wed, 17 Apr 2013 18:29:20 GMT

Hi Deepak,

This is with regard to the Custom OnClearAll document even in Qlikview athttp://qlikshare.com/856.

I have tried the similar function in my file but I am not able to implement that. The function is exactly written the same way as you have been. It works fine till the concat level but when I go for the the $ expansion, it cripples.

Could you provide me with some reasoning for this. Attached is qvw file explaining the situation in the sky blue text box. The Schema is pretty simple with just two fields over there.

The function I am using is

=$(=concat(DISTINCT ‘GetSelectedCount(‘& $Field & ‘)’, ‘ + ‘))

Thanks.

Regards,

Ajit

Re: Cumulative sum

IAMDV — Fri, 28 Jun 2013 10:40:13 GMT

Hi Ajit,

Sorry for the delayed response. You need to use the square brackets.

=$(=concat(DISTINCT ‘GetSelectedCount([‘& $Field & ‘])’, ‘ + ‘))

I hope this helps! Also, if you are using "Always one value selected" then you need to tweak the logic in the expression. It works great for me.

Cheers,

www.QlikShare.com

Re: Cumulative sum

Thu, 21 May 2015 06:51:39 GMT

thanks