Data Type in an Excel Nprinting Report - Qlik NPrinting May 2021 SR3

[alexquimu]

Hey everyone,

I'm running into a data type issue with an NPrinting report in Excel. When I drop a simple measure like

num(sum(Amount), '#.##0,00'))

Excel correctly recognizes it as a number and keeps the formatting. However, I need to apply different KPI and formats based on a field value using a pick(match()) or an if() expression. Here's a sample:

pick(match(Dim, 'Value1', 'Value2'),
    num(sum(Amount), '#.##0,00'),
    num(sum(Amount) / $(vAuxDenominator), '#,##%')
)

The problem is, when I use this expression, NPrinting seems to lose the formatting. In the generated Excel report, the KPI is treated as text. It's aligned to the left, and there's a green symbol indicating it's a number stored as text. This is strange because the KPI is correctly recognized as a number within Qlik Sense. Here's a screenshot from the Excel report:

I don't really have a problem with the alignment, but with the green symbol.

Some of my attempts:

• Enabling/disabling the Keep Source Format option for the object in the NPrinting template.

• Converting the object to a number directly in the Excel template.
• Using num# outside of the if or pick/match function..

Has anyone ever faced a similar scenario as the described? 

Thanks in advance.

Greetings,

Alex

[TheLazyDeveloper]

Have you disabled the keep source format and convert that specific row or column in the excel template to a number? You could also try using a variable in nprinting for that value and see if that holds to format to a number. 

[Lech_Miszkiewicz · Accepted solution]

Hi, 

Yes - your data is a number in Qlik and Qlik is such nice tool that it can handle mixed types in single column. When it comes to Excel that is very different story. 

So there are really 2 solutions:

1. why does the green triangle bother you? is it because people receive excel report and you just dont want to see green triangle or is it because it is a text and you cannot do any further mathematical calculation on it? If it is just a look and people dont use it as actual excel you can consider exporting the same report as PDF (When NPrinting produces PDF from excel template it is same as "Print from excel to PDF" so you may want to consider Printing margins, and page layout etc when making such decision) otherwise you may choose to live with it!
2. second option is what I usually do when data has to be numerical. There is obvioulsy bit of preperation required as in Qlik Sense every column with mixed type measure needs additional column to be created with field representing text value. That text value is later used to format conditionally number using excel conditional formatting. 
   1. Here is simple example with
      1. 1 dimension
      2. 1 measure (that needs to be just numerical value without mixed formats!!!)
      3. and 1 column with format number information.
   2. You bring all 3 columns to NPrinting and disable "Keep Source Format". That allows you now to use all possible excel formatting options (borders, cell colours, font styles and colours and it also allows you to use conditional formatting options
   3. Next thing you do is you use formatting rules on your measure (column D) based on values populated in column C. You need to create as many rules as many formatting options you may need. (If below image is too smal - right click and open in new window - it should come up much larger then... same with all other images)
   4. Thats it, Preview to check if your formatting works, If it does you can HIDE excel column which contains formatting values so it is not visible and only applies as a conditional format helper.

There you go - there you have it. 

It is very well know problem with exporting mixed types to excel and it has been going on for ages since QlikView days so there is nothing unexpected about this behaviour. It is your choice in what you want to do with it now.

cheers

[Lech_Miszkiewicz]

Hi @alexquimu 

I forgot to state the obvious - on Qlik Sense side I build dedicated object just for NPrinting as such object is too messy to be exposed to users.

cheers

[alexquimu]

Hi @Lech_Miszkiewicz

Thank you for the detailed answer. It’s always a pleasure to read your suggestions. Conditional formatting seems like the solution for straight tables, it's really smart to compute a column with the data types. I just have one doubt: what if I use a pivot table instead of a straight table? In that case, I wouldn’t be able to drag columns separately (object is dragged as a whole) and therefore couldn’t apply conditional formatting in the same way. 

Regarding your question (point 1), end users usually download reports using the on-demand extension. They usually export the report to Excel, where the green triangle might be annoying. As far as I know, they don’t perform any mathematical transformations, since these could be done in NPrinting. That’s why I’m only concerned about the green triangle. They need the Excel template as the tables have a lot of columns.

Greetings, 
Alex

[Lech_Miszkiewicz]

Hi @alexquimu 

why do you need pivot table?

cant you export table as straight table data and if view is in pivot format use an excel pivot table to achieve required pivot layout. 
In such case it would be even much easier to get final mixed type layout as that can be handled in excel picot table. It is hard to suggest more without seeing fully required layout, measures and dimensions used in your output. Knowing exact requirements I may be able to suggest different solution. 

btw-with the OnDemand option you can set default format to be PDF so users would really have to individually change format to excel if they wanted to….

cheers