nprinting designer and number formatting

[griffinleow]

Hi all

We have two columns in our dashboard - Currency and amount

For example, currency contains values like JPY, USD, EUR, etc. We want to configure a different number format for amounts that are in JPY i.e. for JPY - '#,##0' and then for others - '#,##0.##', '.', ','

This is showing as intended in QlikSense dashboard. However, when the excel (.xlsx) report is generated in NPrinting Designer, the data type of the amount column is text. Are there any ways to change the data type of the column in NPrinting designer/template editor to number? 

I have actually tried to change the data type from 'general' to 'number' already. But it still does not reflect the change.

Appreciate any inputs.

Thanks

[Daniel_Jenkins]

Hi Griffin,

Difficult to investigate/troubleshoot without the Qlik Sense App. With queries like these it's best to include a simple App that demonstrates the behavior you are experiencing. Having said that, have you tried converting to number format using num#? See: https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpreta...

HTH - Daniel.

[Frank_S]

In addition to @Daniel_Jenkins suggestion, you may also need to update your NP server/engine region/administrative/additional settings as shown below to achieve the objective that you are looking for.

Hope this helps...

[griffinleow]

Hi Daniel and Frank,

Thanks a lot for your inputs. We have not been able to try out your suggestions because we encountered the issue for NPrinting Designer. 

Will let you guys know again on my findings.

Regards,

Griffin

[griffinleow]

Hi all

I have attached the QVF. And I also tried to use num# but to no avail as well.

Is there any workaround to achieve the same outcome?

Thank you

Regards,

Griffin

[Daniel_Jenkins]

Hi Griffin,

Thanks for the App, it makes things easier.

If you don't involve NPrinting at all and just export the chart to Excel from the hub you will see that the format is Text ⓵. NPrinting uses the same method to get the data so you will need to change the format to Number ⓶ or Currency ⓷  etc. in the source Qlik Sense App (perhaps make a copy of the table and format that appropriately for NPrinting reporting). 

HTH - Daniel.

[griffinleow]

Hi Daniel,

Thanks for the informative screenshot.

Could you be more specific on how did you change the number format from text to numeric? 

I've tried to change it by clicking on 'numbers' and 'currency' from the Numbers tab but the amounts are still in text format.

Two more questions: 

1) How do you change the format to number in the QlikSense app?

2) Can I implement the IF condition in NP template editor instead? If yes, how do I do it?

Thank you very much

[griffinleow · Accepted solution]

Hi all,

After much troubleshooting, we have found the solution to our problem which was actually a simple one.

Instead of relying on IF condition incorporated in the field in Qlik Sense, we should have implemented Excel conditional formatting on the 'Transaction Amount' cell in NPrinting Designer Excel template. 

Hope this is useful information!

Thanks

 

[Frank_S]

Further to this, note that there is a 'known limitation' outlined in more recent NPrinting Product Release Notes regarding the following which appear to 'might' also to your situation:

Qlik Sense tables with conditionally shown columns have limited functionality
Jira issue ID: OP-7747: The following functionalities are not supported for Qlik Sense tables that have a "Show column if" condition set on one or more of their columns:

• Levels
• single column export
• keep source formats