calculate currencyrate value to use in column header

[chriscools]

Hello,

i would like to calculate a certain value to use in a column header.

there are two possible values for  "SkuCurrencyCode" : EUR and USD.

I want to calculate the exchange rate for USD to EUR based on the value in the system.

in our ERP system the exchange rate is stated as 0,9 from USD to EUR,

but in qlikview it must be shown as 1,11 EUR to USD.

So i need to recalculate it.

if i do it like this:

=num( avg (1/ SkuCurrencyRate),'#,###0')

i get a value but it changes depending on how much articles with EUR or USD currency that are in the selection.

And of course i need the value to allways stay the same.

so what i really need is to get the value if i divide 1 by the SkuCurrencyRate for SkuCurrencyCode "USD".

i tried it with set analysis like this:

=num( (1/ ( {< SkuCurrencyCode  = {'USD'}   >}   SkuCurrencyRate)),'#,###0')

but i didn't get  a value at all...

Could someone help me out?

thanx!

grtz,

chris

[trdandamudi]

May be this:

=num( {< SkuCurrencyCode  = {'USD'}  >} ( 1 / SkuCurrencyRate) ,'#,###0')

[MK_QSL]

Can you provide 5-6 line sample data along with required output?

[jonas_rezende · Accepted solution]

Hi, chris cools.

Try this:

num( avg ({1} 1/ SkuCurrencyRate),'#,###0')

or

num( avg ({1< SkuCurrencyCode  = {'USD'}   >}  1/ SkuCurrencyRate),'#,###0')

​Hope this helps!

[sunny_talwar]

What if you just try this:

1/SkuCurrencyRate

[chriscools]

Hey Jonas,

this works perfectly!!

thanx!

chris