how can I count cumulative sum value “Y” conditionally value “X”.

[#1]

  Hello,
Could you help me , how can I count cumulative sum value “Y”
conditionally  value “X”.

  I’m
interested  what the formula can I set in pivot table. I’ve attached the
example,  of how this can be done in Excel. The formula for 2 value I
suppose, I can use as:

  if(x=1,

 
RangeSum(above(total (if(x=1, y)), 0, RowNo(total))),

 
RangeSum(above(total (if(x=-1, y)), 0, RowNo(total)))

 
)  

   But
the problem is that the values of X can be a lot and need a universal formula.

Thanks for the help

[Gysbert_Wassenaar]

Calculate it in the script. See attached qvw.

[#3]

thanks for the advice,

But this was a simplified example, actually the dimension “X” of pivot table forms dynamically from several dozens of variants and that is why you script doesn’t solve this problem.

[Gysbert_Wassenaar]

Well, you can try a pick-match combination. See attached qvw. I doubt this will be practical since you'll have to hardcode every x value and every subexpression for each x value.

[qlikpahadi07]

Here is the Simple way :

If(x = Previous(x), RangeSum(y, Peek('Test')), y) AS Test

see the attachment

[#6]

Thank you for your participation, but the script cannot be used for PivotTables.

Please, look at the attached example. As I wrote before , I need make a pivot table , where the dimensions (Х1, X2, X3) form dynamically (possible values: age, sex, family status, region, income, etc. more than 20 variants)

And I need count the cumulate sum for each number of this dimensions (Х1, X2, X3) (look at example)

The script can’t be used , because firstly , different filters can be overlapped on pivot table ,then all that was calculated during the loading will be wrong. And Secondly, the Dimensions X1,X2,X3….Xn – are defined by users . And I can’t hardcode every x value because of a huge number of variations in the task dimension X.

[#7 · Accepted solution]

Hi ,

i did it in chart level ... chk the below expression which i used... hope it helps u...

this one is based on the test.xlsx

=aggr(rangesum(above(sum(y),0,NoOfRows())),x,a)

cheers

kumar

[#8]

That is great! This is what I need. Thanks a lot for idea.

thanks everyone for the help.