topic Re: Adding Specific Rows in Pivot Table in QlikView

Adding Specific Rows in Pivot Table

lain_ — Wed, 24 Jun 2020 21:38:34 GMT

I have a pivot table like the one below. A-F and N and Y are dimensions. The values in the N and Y columns are calculated by an expression. I was wondering if it is possible to get the ? column where for each row, I divide that value by the sum of the values of the last three rows.

	N	Y	?
A	1	7	cell(A, N) / (cell(D, N)+cell(E, N)+cell(F, N))
B	2	8	cell(B, N) / (cell(D, N)+cell(E, N)+cell(F, N))
C	3	9	cell(C, N) / (cell(D, N)+cell(E, N)+cell(F, N))
D	4	10	cell(D, N) / (cell(D, N)+cell(E, N)+cell(F, N))
E	5	11	cell(E, N) / (cell(D, N)+cell(E, N)+cell(F, N))
F	6	12	cell(F, N) / (cell(D, N)+cell(E, N)+cell(F, N))

Re: Adding Specific Rows in Pivot Table

Saravanan_Desingh — Thu, 25 Jun 2020 02:50:50 GMT

One solution is.

tab1: LOAD RowNo() As RowID, * INLINE [ D1, N, Y A, 1, 7 B, 2, 8 C, 3, 9 D, 4, 10 E, 5, 11 F, 6, 12 ]; Left Join(tab1) LOAD Sum(N) As Last3N Resident tab1 Where RowID >= 4; tab2: LOAD *, N/Last3N As Value Resident tab1; Drop Table tab1;

Re: Adding Specific Rows in Pivot Table

Saravanan_Desingh — Thu, 25 Jun 2020 02:51:29 GMT

Output.

Re: Adding Specific Rows in Pivot Table

Saravanan_Desingh — Thu, 25 Jun 2020 03:24:33 GMT

Front End solution.

Dimension: D1

Expression: N/$(=Sum(Aggr(If(RowID>=4,N,0),D1)))