export Issue, KPI is a dimension within a pivot table - how to change most efficiently to a setup with each KPI in separate expressions?

[iliyansomlev]

Hi,

We have a pivot table in Qlik Sense with several KPIs - some are percentages, some quantities (integers), some are amounts (2 decimal places).

The number format cannot be set in the pivot, because the KPIs are present in this table as a dimension. So the number format is set to Auto, and the format is inside the expression, that is loaded as a variable from excel, where all formatting and expression definition are set - please see the attached pic.

In the attached pic, KPI Description Column is in the pivot table as a dimension, and each row provides (in the variable that is the only expression) what formula and number format is calculated for each value of the KPI dimension.

Our issue with this design is that when we export this report to excel, the numbers are exported as text. We cannot convert Pivot to Straight table because the KPI dimension has to be column-wise and the other dimensions row-wise. Can you advise me if there is a way to export them as a number?

If we load each value of the KPI dimension separately and create for each an expression with number format set inside pivot table settings in Qlik Sense (not Auto, but Number), then all of these expressions are exported as numbers. The problem with this approach is that we have many KPIs and will have to manually create more than 100 expressions. If there is no way to export the numbers as numbers in excel under the current design (KPI as dimension and format and definitions set in external file), can you advise me how can we use Qlik Cli or other tool to programmatically write more than 100 similar expressions - only set analysis for KPI.ID will be different in each of them, as well as their number format (set explicitly in pivot table settings), label and show/hide condition. 

We work on Qlik Sense Server on Windows environment . If Qlik Cli can be applied only in SaaS we can move the app there, programmatically write those expressions (as we have data versions, the total number is 475 expressions and 2 times as much variables - for expression definition and label) and then move it back to Windows environment.

Please can you give me advise on the above, so that we are able to export numbers in excel.

Thank you
 

[rpennacchi]

Hello, Iliyansomlev,

I tried to reproduce exporting the pivot table doing what you did, but, in my case, I got numbers on my excel file instead of text as you said. Maybe this happens because of your Qlik version or I didn't do the same process that you did. If you share a QVF file with some data sample it would help.

About editing a lot of expressions at the same time, I think this video can help you (watch from 0:36 up to 4:12):

https://www.youtube.com/watch?v=9lhL3Nrel5Q

This video shows how to open and edit the "metadata"/properties of a pivot table.  By doing that you can edit/create/exclude a lot of measures at the same time. The only thing you need is to identify the parameter you want to change and replace.

[iliyansomlev]

Hi, Rodrigo,

Thank you so much for your answer! The video is truly helpful if we come to the inevitable splitting of the KPI as dimension to many single expressions explicitly formatted in the pivot table properties. I sincerely hope that a solution with our current design is possible, even though we were unable to find such for many weeks searching. I attach here an app for you. Really appreciate your time and help!

Iliyan

[iliyansomlev]

I forgot to mention that we have seen this behavior on all Qlik Sense on Windows versions from 2022 till the latest version.

[rpennacchi]

I've done some tests with your app, but I didn't find a solution 😕 

I think the problem is: you have 2 formats in one measure ('# ##0' and '# ##0.00%') so Qlik don't find a standard about formatting and export everything as a string.

Even in a straight table the problem happens (if you convert the table that you use). It only works if you export the table keeping table formats. But it is not avaible on pivot table 😕

[iliyansomlev]

Thank you again for your time about this riddle. I also think that keeping table formats option in pivot tables is an important feature users would like to have. There should be some reason why it is not there - probably because the pivot is more complex than a straight table. I tried also with third party extensions and it did not work either.

Anyway, what you showed me in the video about using the metadata to more easily write hundreds of similar measures was of great help. We have many sub-KPIs and versions of data, which is the reason for our model to be linked like this and have KPI as a dimension with externally set formats; which unfortunately can be rendered properly only in a pivot table. Now we will split this to about 500 measures, each with separate explicit format set in the panel of the setting for a pivot.

Thank you again,

Iliyan