Expression conditional formatting and export in excel

[claudio1]

Hi,

I'm experiencing the issue explained in this thread:

Number formatting lost in excel export

Summarizing : I have an expression in a chart that can take different types of values (numbers, %, etc.)  and i want to pass this type format to excel when i export with the built in chart XLS export.

For the purpose my expression is left to "Expression Default" on the chart number formatter interface, then i apply the num() function with a mask inside the expression to show this as a number or a percentage based on the dimension value.

Inside Qlikview this works very well and i can get exactly what i want, but when i try to export to excel all the numbers end up as strings on the exported file.

I tried replacing the Num() function with Money(), from what i read on other sources this was the workaround until SR5, but it does not seem to work anymore on SR9.

Anyone know if there is a workaround for this problem in Qlikview 11 SR9+?

Thanks in advance,

Claudio

[#2]

Hi Claudio,

by using the default expression formatting, the result in excel will be raw value, without your specific num function formatting applied. I am not sure there is a native way to stop this from happening.

One option you might consider is to have an expression for each of the possible types of value you will use, and then conditionally enable the relevant expression when it's type is used (so only one is enabled at a time).

You can then set the formatting within the chart itself rather than default expression and when you then export out to excel the expression will have the formatting you want.

Hope that helps

Joe

[claudio1]

Hi Joe,

Yes that solution you provided is the only one i also thought at the moment as possibile solution, the only problem is i have another report with multple dynamic expressions and I could end up with many expressions in a single chart maybe affecting the performance, i will have to test it in advance.

Anyway if i can't find any other "trick" (like the Money() function for example) i think that one is the only option.

Unfortunately I think Qlikview manages these types at a lower level, so the the only way is to set them directly.

Thank you very much for your help, really appreciated.

Claudio

[#4]

Hi Claudio,

from a tidiness point of view you obviously don't want to have to many expressions yes, but from a performance point of view doing this shouldn't have a big impact, as if you have 3 'types' you will have the exact same expression for each expression, so still the caching benefit there.

The extra overhead will come in the form of a conditional expression you will have to put for each one, which should be negligible.

Joe

[claudio1]

That's a good point Joe, shouldn't affect my performance that much indeed.

I think i'll go for that option given that it seems the only one viable.

Thanks again,

Claudio

[#6]

No worries, hope it works well for you