NPrinting excel export of Pivottable and Number formating with measure expression

[Dario_Gee]

Dear community,

I try to export a Pivot Table to excel in NPrinting November 2020 (Version: 20.39.3.0). The Pivot Table has two dimensions and one measure field. I am calculating various measures out of the measure fields relying on one dimension field (island table). My main problem is, that all the KPIs are exported as text. I have not defined any of them as text but all as num() like num(sum(xy),'#'##0') or num(sum(z),'#'#0%). I use IF ... ELSE statments to assign the different measures to the dimension value.

Different KPIs with formatting in the measure itself shown in the Pivot Table:

Number formatting is set to Measure expression (also automatic works fine but not with NPrinting):

If I set the Number formatting to for example number it works, to export as numbers. Unfortunately then my data is not shown in the right way in the Pivot Table and I need to redo the formatting also in excel. I tried to change the cell format in excel (in the NPrinting template) without success. So still have the issue that I don't want my measure exporte as text but with the defined num() values.

Any hints on how I can solve the issue? Thank you

BR,

Da

[Frank_S · Accepted solution]

Hi @Dario_Gee

I'm not sure I fully undestand your requirement. However I suggest that you try to create a Pivot Table in NPrintng designer use the excel template editor.

To prepare for this, you will need to convert the pivot to a straight table:

In your Qlik Sense app:

• make a copy of your pivot table,
• create a straight table object,
• then drop the pivot table copy over the straight table.
• done editing

Next

• Reload your NPrinting connection to the Qlik Sense app
• Create an excel report
• Follow the instruction in the following link to create a pivot table with Qlik Sense data (These instructions also apply to Qlik Sense source data)
  • https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ExcelReports/Pivot-table-Excel.htm

Hopefully this provides you with the results you need...

Kind regards...

ps: others here might have additional ideas for you as well.

[Dario_Gee]

Hello @Frank_S ,

Thank you for your reply. Maybe I provide a bit clearer information. I do not need a pivot table or the functionalities of it in excel. I just want to export the pivot table and having the numbers formatted as what they are represented in the pivot table itself.

My main issue is shown in the screenshot below. If I think about it, maybe it has not only to do with NPrinting but how Qlik Sense handles the export of multiple formats in one measure in general. The export is done by "rightclick" and "download as... data"

I attached an app where this can be reproduced. Can you think about a solution for this case?

BR,

Dario

[Frank_S]

I believe that you may be encountering some known limitations as mentioned in the following link.

• https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Designer-properti...

So again as suggested, you may want to consider creating a pivot table with Qlik Sense data then apply your own formatting to this reconstructed pivot table.

Perhaps others here may have other unique solutions if the above is not suitable to your needs.

Kind regards...