Conditional formatting and exporting to Excel

[sangland2]

Hi, there are a number of posts that deal with problems exporting numbers to Excel which arrive as text. When the number tab is used specifying Money or Fixed this is OK, but if the expression default is used and the expression contains conditional formats then excel receive text which is no good for further calculation in Excel eg:

if(ShowValue='Qty',

Num(sum({<Month={'Aug'}>}[Quanity Delivered]),'#,##0'),

if(ShowValue='Value',

Money(sum({<Month={'Aug'}>}[Line Value]),'$#,##0.00;-$#,##0.00'),

if(ShowValue='GP',

Money(sum({<Month={'Aug'}>}[Line Value]-[Line Cost]),'$#,##0.00;-$#,##0.00'),

if(ShowValue='GPPerc',

Num(sum({<Month={'Aug'}>}[Line Value]-[Line Cost]) / sum({<Month={'Aug'}>}[Line Value]),'#,##0.0%')))))

The workaround for this is to make the format mask a variable. I have buttons which set the variable ShowValue and also a vNumberFormat., so the formatting in the expression is no longer conditional

num(

if(ShowValue='Value',

sum({<Month={'Aug'}>}[Line Value]),

if(ShowValue='Qty',

sum({<Month={'Aug'}>}[Quanity Delivered]),

if(ShowValue='GP',

sum({<Month={'Aug'}>}[Line Value]-[Line Cost]),

if(ShowValue='GPPerc',

sum({<Month={'Aug'}>}[Line Value]-[Line Cost]) / sum({<Month={'Aug'}>}[Line Value])))))

,vNumberFormat)

The export to excel now has numbers instead of text as they display in Qlikview.

NB I believe this was a bug fixed in 11.2 SR2, but I have SR4 and it was still a problem.