topic Re: Number Format Issue in QlikView

Number Format Issue

posywang — Tue, 09 May 2017 19:31:56 GMT

I have attached a sample file for this question. We tried Round and Num function but neither works as desired. We'd like to allow users to control the display of decimal places and keep the format once sending to excel. When using Round, format is kept when sending to excel. However, it shows 11.8 instead of 11.80 when user selects two decimal places. When using Num, it shows decimal places correctly in the table, but format is lost when sending to excel. We can see that some data is exploded to four decimal places. We've been stuck in this issue for a couple of weeks now and would like to ask for suggestion from this forum.Thank you!

Re: Number Format Issue

Anonymous — Tue, 09 May 2017 20:37:08 GMT

=num#(If(vNbrFormat = 'Millions', num(Avg(F2)/1000000,'#,##$(vDecimal2)'), If(vNbrFormat = 'Thousands', num(Avg(F2)/1000,'#,##$(vDecimal2)'), num(Avg(F2),'#,##$(vDecimal2)'))),'#,##$(vDecimal2)')

Re: Number Format Issue

posywang — Tue, 09 May 2017 20:41:45 GMT

Looks like we are one step closer to the solution! Below is what I got from Sending to Excel. Is there a way to keep the two decimal places in Excel? Thanks.

F1	Avg
	42174.03
a	40000.12
i	20000.02
j	35000.1
m	40000.21
n	37500.1
o	50000.19
r	50000.07
s	50000.12
v	70000.12
w	80000.2
y	10000.22

Re: Number Format Issue

Anonymous — Tue, 09 May 2017 20:53:09 GMT

using num# kept the 2 decimal places for me. looking at vDecimal2

=num#(If(vNbrFormat = 'Millions', num(Avg(F2)/1000000,'#,##$(vDecimal2)'), If(vNbrFormat = 'Thousands', num(Avg(F2)/1000,'#,##$(vDecimal2)'), num(Avg(F2),'#,##$(vDecimal2)'))),'#,##$(vDecimal2)')

is that not what you got?

Re: Number Format Issue

RSvebeck — Tue, 09 May 2017 20:54:47 GMT

You might find some hidden Easter egg settings usefull.

Search "Easter Egg" in the forum.

or this link may work as a start:

https://community.qlik.com/mobile/mobile-redirect.jspa?nativeURL=jivecore%3A%2F%2Fcontents%2F365464%3FinstanceUrl%3Dhttps%253A%252F%252Fcommunity.qlik.com%26objectType%3D1&webURL=%2Fmobile%2Fmobile-access.jspa%23jive-content%3Fcontent%3D%252Fapi%252Fcore%252Fv3%252Fcontents%252F365464

Robert

Re: Number Format Issue

posywang — Tue, 09 May 2017 20:59:37 GMT

I used your file and this is what I got in Excel. Is there any configuration that I need to do in my Qlikview application to get the same result?

F1	Avg(in Thoousands)
	42.17
a	40
i	20
j	35
m	40
n	37.5
o	50
r	50
s	50
v	70
w	80
y	10

Re: Number Format Issue

posywang — Tue, 09 May 2017 21:03:43 GMT

I did update one Easter Egg setting - ExcelExportMixedAsText ->Set it to '0' per users request. They couldn't add up Text in Excel.

Re: Number Format Issue

Anonymous — Tue, 09 May 2017 21:11:52 GMT

That would be why. I don't think you're going to be able to force it from Qlikview without sending it to Excel as text or using an external extension like Nprinting.

Excel is formatting as 'General' which automatically drops any zeroes at the end.

If you do find a way, please post it!

Re: Number Format Issue

RSvebeck — Tue, 09 May 2017 21:41:56 GMT

This formula works for Me when exporting to Excel.

=num(

round(num#(10*(num#(1&repeat(0,vDecimals))))*num#(Value),1)/num#(10*(num#(1&repeat(0,vDecimals))))

,'#,'&repeat('#',vDecimals),'')

The vDecimals is a variable - containing a digit defining how many decimals excel should receive. Set it for example to 4, and excel will get 10.0002, or set it to 2 and excel will get 10.00 for the same value.

Adjust . and , depending on your regional settings.

Replace "Value" with your function as you have it in your application.

Good Luck

Robert