Pivot table - Cumulative expression value

[phoenix]

Dimension	calculated  CO1 (chart column)	calculated Delta (chart column)	desired
A	1	1	1
B	2	1	2
C	3	2	3
D	4	2	2 + 4
E	5	2	2 + (2+4)
F	6	3	3 + (2+(2+4))

Hi, I have the above pivot chart, with the Dimension (A,B,C,D,E,F) and calculated chart columns CO1 and delta, my desired column in the same chart is column 'desired'. for the first three dimension's I need the desired to be same as calculated CO1. this is based on a different variable I have in my dataset. For the dimensions D, E and F desired should be a cumulative increase.

For dimension D, it should be CO1 + delta i.e. 2+4. For dimension E, it should be Dimension D's desired value which is (4+2) plus the delta for dimension E, i.e (2+4) + 2. similarly for dimension F.

Is there a simple way or function to do this in pivot table chart? Thanks.

[Sergey_Shuklin]

Hello, Siva!

Try to use this expression: =if(RowNo()>3,RangeSum(Above(sum(delta),0,RowNo())),sum(co1))

Please, see the attachement. Is this what you want?

[Anil_Babu_Samineni]

May be this for desired

If(RowNo()<=3, Sum(co1), RangeSum(Above(sum(delta),0,RowNo(TOTAL))))

[phoenix]

thanks for the rangesum() expression.  It will show correct values for all the rows except for dimension D. for Dimendion D , we need to take CO1+Delta i.e.2+4. but this expression will calculate cumulative of delta column for dimension D i.e.1+1+2+2. since in this example both seem to give same result. but the calculationn should be different for dimension D.

[Sergey_Shuklin]

In that case will help this expression:

if(RowNo()<4,sum(co1),if(RowNo()=4,co1+delta,RangeSum(Above(sum(delta),0,RowNo()))))

[phoenix]

great! that's working. only problem is that row 4 is not always constant. from month to month, it would change, for the next reporting month it would become row5, where the change happens. TO identify the change I am using rank function. so for dimension D, E, F.. rank function would give 1, 2, 3. so for rank 1, which is our row 3, it should be Co1+delta, for anything above rank 1, i.e for rows E & F it should be rangesum(above()). I am havin gdifficult integrating rank, rowno()and rangesum fucntions,

[Sergey_Shuklin]

If you use a Rank() function - place it in the expression:

if(rank(rank_exp)=1,co1+delta,if(rank(rank_exp)>1,RangeSum(Above(sum(delta),0,RowNo())),sum(co1)))

[Sergey_Shuklin]

The one thing you'll need to solve is how to name the A,B,C values. I suppose the rank for them will be inappropriate.

[phoenix]

this is the expression I am using :

if(MAX_LOAN_VINTAGE_MTH>=12,
[2013 CO_1],
if(rank(-if(MAX_LOAN_VINTAGE_MTH<12,TOB_calculated))=1,
[2013 CO_1]+([2013 loss_4]/[2013 LA]),
RangeSum(Above([2013 loss_4]/[2013 LA],0,RowNo()))
)
)
first part of the If condition is working and I am seeing values same as highlighted above in black.

second part of the if condition is also working, where the rank is '1', highlighted in blue

third part of the expression doesn't seem to work, highlighted in red, we should see a cumulative starting from that point. but it is dropping from 1.5052 to 1.4984.   at the point where rank greater than '1', it should take the delta which is 0.0210% and add it to previous row (i.e. rank '1') which is 1.5052%.

[phoenix]

Everything seem to work except for TOB 42.