Handling Opening / Closing Balance in Qlik with different Currencies

[bwisealiahmad]

Hi,

I have a transaction table with transactions in different currencies.

Company X has the Local Currency NOK

Company Y has the Local Currency SEK

Company Z has the Local Currency EUR

What I am trying to achieve is that they can see their balance consolidated after removing eliminations in their different currencies.

So if I press that I want to see it in NOK it has to multiply (fake currencies here):

The par tof the fact table that is in NOK * 1 (Local currency)

The part of the fact table that is in SEK * 1,2 (turns this into NOK)

The part of the fact table that is in EUR / 8 (turns this into NOK)

If I wanted to see it in EURO it would be

The part of the fact table that is in NOK * 8 (Now in EURO)

The part of the fact table that is in SEK * 8,8 (Now in EURO)

The part of the fact table that is in EUR * 1 (Local Currency)

Just re-calcaulating the transaction history doesn't work here so I am trying to understand how I can do this in front-end or solve it otherwise. I have currencies daily and monthly from NOK to SEK, EUR, DKK, GBP (the currencies I want to see this in).

Hope that explains it.

Best,

Ali A

[JustinDallas]

The only way I can think of how to do this is fairly ugly.  But it would probably begin as:

If( SelectedCurType = 'EUR',
    SUM({<CurType={'NOK'}>} TxAmt) * 8 + SUM({<CurType={'SEK'}>} TxAmt) * 8.8 + SUM({<CurType={'EUR '}>} TxAmt) * 1,
    If( SelectedCurType = 'SEK',
        ....

I'm not sure if the aggregate function of SUM would work but that's where I would start.  Of course, you have to make sure you flag your transactions with the currency type they belong with.  Also, NOK?  Who is doing business with North Korea?!

[bwisealiahmad]

LOL.

First of all. Thank you. That is also what I have been thinking, but I am trying to see if someone else has a better way of solving this.

And NOK = Norwegian Krone...

You just made me laugh. Cheers for the answer.

[JustinDallas]

Let me know if it works, I kind of did that off the cuff.

[YoussefBelloum]

LMAO.. Man I was trying to help.. now I'm dead

[bwisealiahmad]

LOL made me laugh too.

Jesus

[YoussefBelloum]

Hi Ali,

It would be much more interesting for all of us if you share more info.. like your Model, your tables structure after the reload (you're looking for a solution on the front-end).

or if you can attach some rows (but FORGET NOK!)

[tomovangel]

I have used the same method, but with CurrencyRates coming from external excel file, that I update daily in the morning.

and its something like

if(SelectedCurType = 'EUR', 

    SUM({<CurType={'NOK'}>} TxAmt) * CurrRates +

      SUM({<CurType={'SEK'}>} TxAmt) *  CurrRates +

SUM({<CurType={'EUR '}>} TxAmt) * 1, 

    If( SelectedCurType = 'SEK', 

        .... 

[ogster1974]

I would have a LC field and RC field the RC field being generated at source or during load.

Then can convert to other currencies from the RC value in the front end via the setting of a variable.

Regards

Andy

[bwisealiahmad]

Well I kinda made it work, but ran into a different issue so might ahve to  rethink this.

To calculate my opening balance I have the following expression:

LET exprSum.Faktatype.Rapport.Firma.FraDato.TilDato = 'Sum({$<' & '$' & '(=$(flt.IgnorerTabell(Kalender))), Faktatype={"$1"}, Rapport={"$2"},%CompanyKey = {"$3"}, %DateValueField={">=$4<=$5"}>} Beløp)';

which could look like this if written without parameters.

Sum({FactType = {'Finance'}, Report = {'Balance'}, %CompanyKey = {'F0001'}, %DateValueFIeld = {'>=0<=X'}>}Amount)

The issue is that this sums up all rows up until a date, but now if I use an IF statement...it is almost as if the IF statemenet cancels the expression the set analysis creates.

I have transactions I am summing up all the way from 2000 which works as it is in the original expression,but if  I add

IF(Currency = 'NOK',

Sum({FactType = {'Finance'}, Report = {'Balance'}, %CompanyKey = {'F0001'}, %DateValueFIeld = {'>=0<=X'}>}Amount) * 0.75,

)

It only Sums transactions from the actual year I have chosen, even tho the expression is meant to Sum up the entire history.

Any ideas or help?