Data Export to Excel from QlikView

[Anonymous]

Hi All

I have created a dashboard in QlikView and exporting it to Excel Sheet.Later if I try to do Sum in Excel by applying some condition,it is returning 0.But for some other sheets, it is returning values correctly.Can anyone please help?

[Anonymous]

Hi

I have used number format in QlikView expression itself as

if(CurrencyCode = 'USD', Num(sum(aggr(avg(Net),[Account Number],[Service Description])) + sum(aggr(avg(Amount),[Account Number],[Service Description])),'$#,##0.00'),and kept Number format as Expression Default in pivot table.Later I have exported pivot table data to Excel.Here I am facing a problem like when I try to sum values in one column then it is returning 0.I think this is due to data is in the form of text instead of being in numbers.But I am unable to convert it.

Attaching sample app.

[annafuksa1]

Ok so problem is what if the currency is not 'USD'

try

=if(CurrencyCode = 'USD', Num(sum(aggr(avg(Net),[Account Number],[Service Description])) + sum(aggr(avg(Amount),[Account Number],[Service Description])),'$#,##0.00;-$#,##0.00')

,Num(sum(aggr(avg(Net),[Account Number],[Service Description])) + sum(aggr(avg(Amount),[Account Number],[Service Description])),'#,##0.00;-#,##0.00'))

[Anonymous]

I am mentioning just one form pf expression,I have many currency codes like that and if there is no currency code I have given condition like you mentioned

[adamdavi3s]

Could you possibly provide us with a set of sample data from a table box? I don't understand why excel is seeing your Euro as a number stored as text, but the dollars as just text.

Actually, it seems to be an excel feature! I've just done the same test, my euros are fine but my dollars are not, bear with me I can come up with a solution

[adamdavi3s]

OK, I think excel is stuffing you here, its everything except dollars it likes.

Even this which replaces the text $ and forces the currency number type doesn't work:

=pick(match(Currency,'GBP','USD','EUR'),num(sum(amount),'£#,##0.00'),num(sum(replace(amount,'$','')),'$#,##0.00'),num(sum(amount),'€#,##0.00'))

See the attached document if anyone wants to play