topic Pivot table question? in QlikView

Pivot table question?

Mon, 25 May 2009 21:06:55 GMT

Hello all,

I would like to ask you to help me with the following issue.

I'm wondering if this is possible to create the attached pivot table dynamically?

Fields: Month and Country are taken from the QV database

KPIs: calculation expression for each category

Categories: static list of values

Thanks in advance for your help.

Kind regards,

JTPro

Pivot table question?

Oleg_Troyansky — Mon, 25 May 2009 21:25:08 GMT

Yes, you can do it, in many ways... One possible way is this:

In your load script, you load a field for Categories with 5 values, and attach a 1/0 flag to each category
Similarly, you load the list of KPIs into a field, and attach a 1/0 flag to each KPI
Then, you can load your formulas for each Category and KPI from an Excel spreadsheet
Then, you can process the list of formulas one by one and generate a single expression combining them all in the following way:


<Category1_KPI1_Formula>*Category1_Flag*KPI1_Flag +
<Category1_KPI2_Formula>*Category1_Flag*KPI2_Flag +
<Category1_KPI3_Formula>*Category1_Flag*KPI3_Flag +

Store this long expression text in a variable - for example exp_Cat_KPI.

In your chart, use a $-extension to evaluate your expression:


=$(exp_Cat_KPI)

good luck!

Oleg

Pivot table question?

Tue, 26 May 2009 20:13:40 GMT

Hi Oleg,

Thank you very much for your help.

I have one more question regarding pivot table column formating.

Can you help me to figure out how to create proper formating in the pivot table as it is presented in the attached file?

Is there any way to format each column in the pivot table separately?

Thanks in advance for your help.

Kind regards,

JTPro

Pivot table question?

Tue, 26 May 2009 20:43:36 GMT

Hi,

After short time of looking into reference manual, I have found something like "Customer Format Cell". It gives me exactly what I want to have.

Kind regards,

JTPro

Pivot table question?

Thu, 28 May 2009 22:33:55 GMT

Hi Oleg,

I have implemented method suggested by you.

The problem is that in my case the number of categories is equal to 75 and the number of KPIs is equal to 3. Additionally I have two more dimensions: month and region. The number of record is counted in millions.

I have the performance issue.

Is there another, faster way to achieve my goal?

Thanks in advance for your help.

Kind regards,

JTPro