topic Re: Pivot table, with calculated column in QlikView

Pivot table, with calculated column

Fri, 26 Apr 2013 13:03:23 GMT

Hi,

This is an actualization of a previous post, Thanks to Gysbert Wassenaar to solve one of my problems.

I have this entry data

Month	DataType	Value1	Value2
01	R	10	1
01	O	12	1
02	R	11	1
02	O	15	2

and I would like to obtain this result

Month	Total			02			01
DataType	R	O	%	R	O	%	R	O	%
Value1	21	27	77,78%	11	15	73,33%	10	12	83,33%
Value2	2	3	66,67%	1	2	50,00%	1	1	100,00%

where the rows Value1 and Value2 are expresions and the colum % is an aditional value of the dimension DataType

Value1 = sum(Value1) in % column sum({<DataType={'R'}>} Value1) / sum({<DataType={'O'}>} Value1)

Value2 = sum(Value2) in % column sum({<DataType={'R'}>} Value2) / sum({<DataType={'O'}>} Value2)

but the result obtained is this

Month	Total		02		01
DataType	R	O	R	O	R	O
Value1	21	27	11	15	10	12
Value2	2	3	1	2	1	1

Any suggestions?

Re: Pivot table, with calculated column

Gysbert_Wassenaar — Fri, 26 Apr 2013 13:40:27 GMT

See attached example.

Re: Pivot table, with calculated column

Mon, 29 Apr 2013 09:11:51 GMT

Gysbert, Thanks for this solution. It's working fine, but the number of records generated is too high. We are working with millions of records (and Value1, Value2 are really Value1, Value2 .. Value60) and inplementing this solution, the application performance is greatly deteriorated. Is there any other way to get the desired result without greatly increasing the number of records?

Re: Pivot table, with calculated column

Gysbert_Wassenaar — Mon, 29 Apr 2013 11:20:51 GMT

See attached example. No idea how this holds up under millions of records. You may have to resort to creating summary tables in the script.

Re: Pivot table, with calculated column

Mon, 29 Apr 2013 15:15:32 GMT

Gysbert, Thanks for this solution. It's working fine.