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