Problem Exporting to Excel AS Numbers

[MalcolmCICWF]

I'm having a problem exporting a Pivot table to Excel. The problem is that in order to make the below layout work, we had to create a field called Column_Field that contained our years as well as Other totals and percentages. Because of the percentages and the fact they are coming from one field, I had to create an expression to format them specifically.

if(row_fields = 'Gross IRR'
or row_fields = 'Net IRR', num(sum(BL_Value)/sum(investment), '#,###.##%'), num(sum(amount),'#,##0'))

Now in the Number tab the format is set to "Expression Default" format, so when we export to Excel it is in a text format... The only way I know of to fix this is to change it to "number" format, which fudges the Percentage formatting in the report.

Anyone ran into this or can think of a way around it? Much appreciated.

[chrismarlow]

Hi,
If you have a fixed set of dates (or at least fixed number of dates) would an alternative be to use a set of variables or date table then use set analysis & multiple different expressions, you could then set the format at the expression level.
Cheers,
Chris.

[MalcolmCICWF]

if you set them in the espressions then you can't export to Excel without it being text and not numbers.