topic Re: Gross Amount Calculation by Currency in QlikView

Gross Amount Calculation by Currency

AH — Tue, 06 Jan 2015 21:01:45 GMT

Hi,

I have different currency in my dashboard and i have to calculate the gross amount by each currency. I did that previously by sales type but now i have to do that based on currency. I have currency like , USD, GBP, EUR, DKK etc.

I used this expression previously: =fabs(num(Sum({<SalesType = {'FT'}>}TotalLineAmount),'##,##0'))

Any Suggestions?

Thanks,

Shan

Re: Gross Amount Calculation by Currency

Tue, 06 Jan 2015 21:11:17 GMT

=fabs(num(Sum({<SalesType = {'FT'},currency={'USD'}>}TotalLineAmount),'##,##0'))

above grossamount for salestype FT for USD only

=fabs(num(Sum({< currency={'USD'}>}TotalLineAmount),'##,##0'))

just currency USD

or you can try using total

=fabs(num(Sum( TOTAL {<SalesType = {'FT'},currency={'USD'}>}TotalLineAmount),'##,##0'))

above grossamount for salestype FT for USD only

=fabs(num(Sum (TOTAL {< currency={'USD'}>}TotalLineAmount),'##,##0'))

Re: Gross Amount Calculation by Currency

AH — Tue, 06 Jan 2015 21:23:50 GMT

Hi Mario,

Thanks for your Nice reply. I want to apply that in Chart. How do i do that for all the currency in a single chart?

Thanks,

Shan

Re: Gross Amount Calculation by Currency

Tue, 06 Jan 2015 21:33:25 GMT

You can create a variable like

vCurrency = Only(Currency) and have 'Always one selected' option checked in the currency list box.

Now in you expression use this variable

=fabs(num(Sum({< currency={"$(vCurrency)"}>}TotalLineAmount * ConversionFactor),'##,##0'))

I am sure there would be a conversion factor for this to work so you might have to probably use that as well.

Thanks

Re: Gross Amount Calculation by Currency

AH — Tue, 06 Jan 2015 21:36:18 GMT

Conversion Factor? Does that mean exchange rate?

Thanks,

Shan

Re: Gross Amount Calculation by Currency

Tue, 06 Jan 2015 21:36:52 GMT

Have you tried to just put Currency as a dimension and then as expression sum(whateveryouwant)

Re: Gross Amount Calculation by Currency

Tue, 06 Jan 2015 21:39:01 GMT

Yes. Say you have your actual transaction in USD and to covnert it into other currencies you need an Exchange rate table that needs to be associated with the Data Model for this to work.