Formatting a formula in NPrinting as currency

[JTieskotter]

I am working on an NPrinting report to provide an Excel report. This is using levels to break down the elements the way it needs to be presented. One of the issues is with the amount totals for each level. Pulling this from the table generates the same level numerous times rather than calculating to that level. I resolved  this issue by using a formula to create the amount using SUM(AMOUNT). This provides the totals I need, but the formatting is not in currency or money. I tried using NUM(SUM(AMOUNT)) and MONEY(SUM(AMOUNT)). This provides a number, but it will not format the number as currency and the Excel formatting does not apply to the report. Outside of creating a new table within the Qlik Sense application is there a way for the formula to be formatted.

[Lech_Miszkiewicz]

Hi, Disable keep source format and use excel formatting:

https://help.qlik.com/en-US/nprinting/June2020/Content/NPrinting/ReportsDevelopment/Designer-propert...

It is little bit unclear what you want to do. When you say: " This provides a number, but it will not format the number as currency and the Excel formatting does not apply to the report" do you mean that you dont want to use excel formatting, or you tried to use it but it did not work?

Regardless - It would be good to see screenshots of your template with NPrinting designer properties panel open for fields you are trying to format, screenshot of the current result and maybe mockup of what you want to achieve.

thanks

[JTieskotter]

That is what i would normally do, but in this case its a formula that is not created within the Excel document. Below is a screen of the formula amount. I have set the excel format to currency, but when it prints the report it allows ends up being a number. I have even tried to put the formatting within the formula in hopes that would resolve the issue without any luck. That is where I tried using MONEY(SUM(AMOUNT)) or NUM(SUM(AMOUNT)). My guess is that you cannot format a number from a formula in NPrinting unless the formula is within the Excel document itself. The problem in this case is that if I do that it will not sum up correctly and will provide all the sum amount present in the table. I was hoping to avoid making another sheet with a table on it just specifically for this report.

[Lech_Miszkiewicz · Accepted solution]

Hi @JTieskotter 

Ok - in this particular case you probably are experiencing known bug. NPrinting July 2020 Patch 1 should already solve it. 

now the workaround would be to  wrap your formula in Text() function like: Text(MONEY(SUM(AMOUNT)))   

[Lech_Miszkiewicz]

The other solution is to apply excel formula which typically is:

Sum(E2:E10)/X

Where X is number of subtotals you have in your report. This simple trick can help you greatly..

Last small note: i typically leave a gap between levels and use <deleterow> tag . That trick allows me to use excel sum() function on different levels