# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Honored Contributor III

## Currency help based on Selection

Hi Folks,

I'm having currency Table like below:

 Plant Jan Feb Mar Apr A 0.0123 0.0124 0.0125 0.0126 B 1 2 3 4 C 0.1 0.2 0.3 0.4

For Plant A exchange rates are .0123 for Jan , .0124 for Feb and so on...

Now if I select any one Month , then my result is working fine.

Suppose I select Multiple Months say Jan , Feb , Mar

Then Revenue should be multiply with Mar Exchange Rate, in case if I  select Mar and Apr together then exchange rate should be from Apr.

So we have to take always the latest Month as per selection.

My expression is = Sum(Revenue)*Exchange_Rate

Regards,

Amit

1 Solution

Accepted Solutions
MVP

## Re: Currency help based on Selection

Hahahaha okay... use this expression

Sum([Net sales without tooling]) * FirstSortedValue(Exchange_Rate, -Sort_Month)

Ignored selection in Month field for the Sum expression... I didn't know how to check with your numbers after making selections. You should have been able to fix that.... Anyways see if this works

17 Replies
MVP

## Re: Currency help based on Selection

May be try this

Sum(Revenue*Exchange_Rate)

Sum(Revenue)*Exchange_Rate

Honored Contributor III

## Re: Currency help based on Selection

Hi Sunny,

Sorry none of them are giving me correct Output.

Now u can see above I have selected Jan And Feb together so the required Net Sale output should be "10613.993125233" (Multiply with Feb Rate) , as I mentioned in requirement that it should take latest month out of selected Months and when I'm selecting just 1 Month Say Jan then Net Sale output should be Sum([Net Sales...]) * 1.063125 , which is Jan exchange rate

Similarly , when user has selected 3 Months - Jan , Feb , Mar ,in such case Sum([ Net Sales....]) should be multiply with March Exchange_Rate (Yellow Marked), as March is latest Month in compare to Jan and Feb

Hope this is clear!

Thanks,

AS

MVP

## Re: Currency help based on Selection

May be this

Sum(Revenue)*FirstSortedValue(Exchange_Rate, -Month)

Honored Contributor III

## Re: Currency help based on Selection

Sorry with this expresion table is not showing anything!

MVP

## Re: Currency help based on Selection

Can you share a sample?

Honored Contributor III

PFA!

Thanks,

AS

MVP

## Re: Currency help based on Selection

Your Month field is not read as a date field... try this

Sum({<Month>}[Net sales without tooling]) * FirstSortedValue(Exchange_Rate, -Sort_Month)

Honored Contributor III

## Re: Currency help based on Selection

See this:

When I select Jan and Feb only , result should be as Yellow Marked , but with your expression it is different.

Thanks,

AS

MVP

## Re: Currency help based on Selection

The app that you have shared with me is showing that you would want 15580.914