Number Format Issue

[posywang]

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!

[Anonymous]

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

[posywang]

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

[Anonymous]

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?

[RSvebeck]

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%...

Robert

[posywang]

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

[posywang]

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

[Anonymous]

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!

[RSvebeck]

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