Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excel Export - Number Formatting

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.

1 Solution

Accepted Solutions
boorgura
Specialist
Specialist

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.

View solution in original post

8 Replies
Not applicable
Author

BUMP

Anybody have any ideas?

biester
Specialist
Specialist

Have you already tried to change the Default Export Options => Number Formatting in the User Preferences?

Rgds,
Joachim

boorgura
Specialist
Specialist

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.

Not applicable
Author

Hi

I hadn't tried that, but now I have and it makes no difference.

Not applicable
Author

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

Not applicable
Author

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,

Not applicable
Author

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,

nboccassini
Partner - Creator
Partner - Creator

I've solved with:

Num#(Num(field,'format'))