topic Re: Excel Export - Number Formatting in QlikView

Excel Export - Number Formatting

Wed, 19 May 2010 13:49:28 GMT

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.

Excel Export - Number Formatting

Thu, 20 May 2010 14:08:26 GMT

BUMP

Anybody have any ideas?

AW:Re: Excel Export - Number Formatting

biester — Thu, 20 May 2010 14:29:28 GMT

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

Rgds,
Joachim

Excel Export - Number Formatting

boorgura — Thu, 20 May 2010 14:46:42 GMT

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.

AW:Re: Excel Export - Number Formatting

Thu, 20 May 2010 14:47:26 GMT

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

AW:Re: Excel Export - Number Formatting

Thu, 20 May 2010 14:52:26 GMT

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

AW:Re: Excel Export - Number Formatting

Thu, 20 May 2010 14:56:22 GMT

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,

AW:Re: Excel Export - Number Formatting

Thu, 20 May 2010 15:13:08 GMT

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,

Re: Excel Export - Number Formatting

nboccassini — Fri, 24 Jul 2020 09:39:53 GMT

I've solved with:

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