Cumulative sum

[#1]

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 ?

[IAMDV]

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,

DV

www.QlikShare.com

[swuehl]

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).

[jagan]

Hi,

PFA file, hope it helps you.

Regards,

Jagan.

[#5]

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

[#6]

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 !

[#7]

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

[IAMDV]

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,

DV

www.QlikShare.com

[#9]

thanks