4 Replies Latest reply: Apr 29, 2013 11:15 AM by Jairo Martín Miguel

# Pivot table, with calculated column

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

See attached example.

• ###### Re: Pivot table, with calculated column

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

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

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