Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Re: Data Export to Excel from QlikView

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.

Highlighted
Creator III
Creator III

Re: Data Export to Excel from QlikView

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'))

Highlighted
Creator
Creator

Re: Data Export to Excel from QlikView

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

Highlighted
Master
Master

Re: Data Export to Excel from QlikView

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

Highlighted
Master
Master

Re: Data Export to Excel from QlikView

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