topic Multiply IF... in QlikView

Multiply IF...

Mon, 18 Apr 2011 21:22:27 GMT

Dears,

I need to do something difficult for me, but simple for you!

I would like to insert an Imput Box with the actual currency value ($, Euro and Reais). In the column D of my spreadsheet, called Currency, I have this 3 options. In the column E, I have the value corresponding for each currency.

What I need to do is. If the currency is Euro, the QlikView should multiply the value in the column E by the corresponding currenvy value in the Imput Box.

So, the propose if to have all the values in the same currency.

How can I do it?
Can you help me?
Can you do an example?

Thanks again for all your support.
QlikView is the best, ever.

And the forum is the best too..

Bye

Multiply IF...

IAMDV — Mon, 18 Apr 2011 21:33:26 GMT

Dear Kellyhbastos,

You are right! Qlikview is awesome and this community is very helpful.

I can certainly spare sometime to help with your issue. However can you please load a QV document with sample data? That will be much easier to provide solution.

Multiply IF...

Anonymous — Mon, 18 Apr 2011 21:41:59 GMT

The painless way to do it is not to use column D at all. Keep your currency selection somewhere with your other filters.
See my reply here:
http://community.qlik.com/forums/t/34373.aspx

Multiply IF...

Tue, 19 Apr 2011 21:20:05 GMT

Hi DV,

Thanks for all your attention,

Follow attached is an example.

What I need to do is:

Since I have datas from 3 differente currencys, and I need to show the total amount of money movimented in this period. I must multiply the currency in EUR by the equivalence in USD, in order to show all the values in USD.

In the attached file there is an Excel spreadhsheet, in the column A there is the Currency and in the collumn B there is the value.

QlikView must multiply the EUR value by the convert number in the Imput Box of QlikView, and the same for Reais values.

Thanks for your attention again
Best regards,

Bruno Lelli

Multiply IF...

johnw — Tue, 19 Apr 2011 22:33:05 GMT

I believe what you're asking for is this:

sum(Value*pick(match(Currency,'EUR','USD','REAL'),EUR,USD,REAL))

Or perhaps more efficiently:

sum(aggr(sum(Value)*pick(match(Currency,'EUR','USD','REAL'),EUR,USD,REAL),Currency))

Or perhaps most efficently (hard to say if this or the previous would be faster):

rangesum(sum({<Currency*={'EUR'}>} Value)*EUR
,sum({<Currency*={'USD'}>} Value)*USD
,sum({<Currency*={'REAL'}>} Value)*REAL)

None of which are how I'd handle currency conversion, but your requirements may be different than normal.

Multiply IF...

spividori — Tue, 19 Apr 2011 22:48:32 GMT

Hi.

See the example, hope this help!
It is similar to the example of Jon.

Regards.