topic Re: Calculation Help (Qlik Sense) in QlikView

Calculation Help (Qlik Sense)

fcecconi — Mon, 07 Dec 2015 14:34:48 GMT

Hello

I am trying to create a summary table of revenue values my company codes for the year. The revenue needs to be converted to USD. When I leave the individual currency lookup key in the table, I get the correct value. However when I remove the lookup key, the value will not calculated.

thanks in advance for your help

Frank

Re: Calculation Help (Qlik Sense)

lironbaram — Mon, 07 Dec 2015 15:03:36 GMT

can you post your expression and dimensions of the table

Re: Calculation Help (Qlik Sense)

fcecconi — Mon, 07 Dec 2015 15:13:07 GMT

The calculation is (Sum([Amount in doc. curr.])*[Actual Rate])/1000

There is a join from the fact to the exchange rate table (Currency Lookup column)

Re: Calculation Help (Qlik Sense)

lironbaram — Mon, 07 Dec 2015 15:26:09 GMT

those your dimension has multiple records of [Actual Rate]

if so you'll need to use an aggr expression something like:

sum(aggr((Sum([Amount in doc. curr.])*[Actual Rate])/1000,currency_key,Yourdimensions))

Re: Calculation Help (Qlik Sense)

maxgro — Mon, 07 Dec 2015 15:29:14 GMT

Sum([Amount in doc. curr.]*[Actual Rate]) / 1000

Re: Calculation Help (Qlik Sense)

fcecconi — Mon, 07 Dec 2015 16:22:06 GMT

Liron

thanks. Here is what the calculation ended up:

sum(aggr((Sum([Amount in doc. curr.])*[Actual Rate])/1000,[Currency lookup],[Currency Code],

[Fiscal year],[Company Code]))*-1