topic Re: SumProduct Between Columns in Pivot Table in QlikView

SumProduct Between Columns in Pivot Table

lain_ — Wed, 24 Jun 2020 21:49:21 GMT

I have a pivot table like the one below. The values are calculated with expressions. Is there any way to get the sum product of the N and Y columns as shown in the ? column. The goal is to eventually graph the sum product while knowing its corresponding (N, Y) and (X, Z).

		N	Y	?
X	A	1	4
	B	2	5
	C	3	6	(14)+(25)+(3*6)
Z	A	2	5
	B	3	6
	C	4	7	(25)+(36)+(4*7)

Re: SumProduct Between Columns in Pivot Table

Saravanan_Desingh — Thu, 25 Jun 2020 02:33:20 GMT

One Solution is.

tab1: LOAD * INLINE [ D1, D2, N, Y X, A, 1, 4 X, B, 2, 5 X, C, 3, 6 Z, A, 2, 5 Z, B, 3, 6 Z, C, 4, 7 ]; Left Join(tab1) LOAD D1, Evaluate(Concat(N&'*'&Y,'+')) As Exp Resident tab1 Group By D1;

Re: SumProduct Between Columns in Pivot Table

Saravanan_Desingh — Thu, 25 Jun 2020 02:34:32 GMT

Output.

Re: SumProduct Between Columns in Pivot Table

Saravanan_Desingh — Thu, 25 Jun 2020 02:42:08 GMT

FrontEnd solution.

Case1:

Dimension : D1

Expression: Sum(N*Y)

Case2:

Dimension : D1, D2

Expression: Sum(TOTAL <D1> N*Y)

Re: SumProduct Between Columns in Pivot Table

lain_ — Thu, 25 Jun 2020 16:53:43 GMT

Thank you for the response. The logic makes sense but for some reason it still doesn't work. I thought the simplified example could be easily extended. I did sum(total <Dim1> Expression1.1*Dim2 ) but it says that Expression1.1 is a bad field name. It would be nice to eventually get the second table to then graph.

	Dim3	N	Y
Dim1	Dim2	Expression1.1	Expression1.2	?	?
A	1	1	5
	2	2	6	(11)+(22)	(15)+(26)
B	1	3	7
	2	4	8	(13)+(24)	(17)+(28)

	N	Y
A	(11)+(22)	(15)+(26)
B	(13)+(24)	(17)+(28)

Re: SumProduct Between Columns in Pivot Table

Saravanan_Desingh — Thu, 25 Jun 2020 19:00:28 GMT

Can you paste your code here? Also how your input looks like? Whatever you have pasted here is from Excel I guess.

Re: SumProduct Between Columns in Pivot Table

lain_ — Thu, 25 Jun 2020 20:30:11 GMT

Everything in the table is what I have in the QlikView pivot table. The dimensions are essentially, Dim1(A, B), Dim2(1, 2), Dim3(N, Y). Dim1 and Dim3 are strings and Dim2 is numbers. The expression calculates another value using this expression: sum(some_info)/sum(total <Dim1> some_info). This calculates the proportions of some_info grouped by Dim1.

	N	Y
A	(11)+(22)	(15)+(26)
B	(13)+(24)	(17)+(28)