Pivot table question?

[#1]

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

[Oleg_Troyansky · Accepted solution]

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

[#3]

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

[#4]

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

[#5]

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