topic Re: Pivot table - Cumulative expression value in QlikView

Pivot table - Cumulative expression value

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Pivot table - Cumulative expression value

Sergey_Shuklin — Fri, 19 May 2017 18:48:25 GMT

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?

Re: Pivot table - Cumulative expression value

Anil_Babu_Samineni — Fri, 19 May 2017 18:56:19 GMT

May be this for desired

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

Re: Pivot table - Cumulative expression value

phoenix — Fri, 19 May 2017 19:01:51 GMT

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.

Re: Pivot table - Cumulative expression value

Sergey_Shuklin — Fri, 19 May 2017 19:05:26 GMT

In that case will help this expression:

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

Re: Pivot table - Cumulative expression value

phoenix — Fri, 19 May 2017 19:12:03 GMT

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,

Re: Pivot table - Cumulative expression value

Sergey_Shuklin — Fri, 19 May 2017 19:25:34 GMT

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

Re: Pivot table - Cumulative expression value

Sergey_Shuklin — Fri, 19 May 2017 19:30:41 GMT

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.

Re: Pivot table - Cumulative expression value

phoenix — Fri, 19 May 2017 19:36:56 GMT

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

Re: Pivot table - Cumulative expression value

phoenix — Fri, 19 May 2017 19:45:37 GMT

Everything seem to work except for TOB 42.