Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks
I have QlikView applications that allow our end-users to control Units (Millions/Thousands/Units) and Decimal Places (0,1,2 or 3) for every number, it also allows them to control number of decimal places for percent values.
I have, for example, a variable called vNumberFormat which will contain the following: #,##0.00;(#,##0.00);-
I have another variable for units called vDivisor which will contain the number 1000000, 1000 or 1
The expression is something like: =Num(Sum(Value)/vDivisor,vNumberFormat)
This works perfectly within QlikView and gives the end-users ultimate control of visual output.
The problem...... when we export any of those objects to Excel (which our Finance dept users do every other minute), all the formatting is removed and we have straight numbers with endless decimal places, and any percentages arrive in excel in native number format.
Does anybody have any idea of how I might be able to provide both the object formatting and the excel output formatting?
Ideas very welcome.
Nigel,
not the ideal way, but you can right click on the Table,
[Copy To Clipboard] --> Full Table Area
Paste it into Excel. Formatting will be in tact by doing this.
Let me know if this resolves your issue.
BUMP
Anybody have any ideas?
Have you already tried to change the Default Export Options => Number Formatting in the User Preferences?
Rgds,
Joachim
Nigel,
not the ideal way, but you can right click on the Table,
[Copy To Clipboard] --> Full Table Area
Paste it into Excel. Formatting will be in tact by doing this.
Let me know if this resolves your issue.
Hi
I hadn't tried that, but now I have and it makes no difference.
Hi
You might need to tell QlikView how you want the format in the properties of the number section? You will need to modify them with a macro instead of your more dynamic num() function.
To start with - does it work if you set the properties manually?
Regards
Juerg
Hi Rocky
Yeah, that works, my end-users will hate me from here to eternity (actually, no change there) for that solution but at the moment its the best I've got to offer.
Thanks,
Hi Juerg
The manual method of setting number formats definately works, so setting by macro would be an option, but it's really not one I would consider as we're talking about hundreds of objects where many fields in each object requires formatting changes. Appreciate the suggestion but I'm afraid I'm not heading down that road.
For the time being its going to have to be the solution from Rocky, hopefully if the end-users allow me to live I'll be around when QlikTech decide to correct it.
Cheers all,
I've solved with:
Num#(Num(field,'format'))