Unable to Sum when using pivot in excel download data, downloading as string instead of number

[Veerendra_Katikala]

Hi Experts,

I have one requirement when the value is null or 0 then display as 4 decimals else 2 decimal precision.

I tried following conditions to format my field, it's working fine in Qlik but when user download the the data in excel , this field downloading as string instead of number hence unable to perform Sum analysis.

1) if(IsNull(TotalSpend) or TotalSpend=0, num(TotalSpend,'#,##0.0000'),
num(TotalSpend,'#,##0.00')) as TotalSpend

2) num(TotalSpend, if(IsNull(TotalSpend) or TotalSpend=0,'#,##0.0000','#,##0.00')) as TotalSpend

Could you someone please suggest?

attached is the sample data attached and issue snapshot.

Note, I tried to use this condition in script, front end , as dimension / measure ..

[Anil_Babu_Samineni]

have you tried to incorporate the format using Num#()?

if(IsNull(TotalSpend) or TotalSpend=0, num(Num#(TotalSpend),'#,##0.0000'),
num(Num#(TotalSpend),'#,##0.00')) as TotalSpend

[Veerendra_Katikala]

@Anil_Babu_Samineni Yes I tried but it's not working 

[Lech_Miszkiewicz]

Hi,

excel does not support multi format for single output which is why this is an issue. You have a column with mixed format applied '#,##0.0000' and '#,##0.00'!

Using native number settings will work but will prevent you from having mixed formats:

as an alternative use excel and multiply by 1 your column