17 Replies Latest reply: Jul 11, 2017 11:58 AM by Amit Saini

# 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

• ###### Re: Currency help based on Selection

May be try this

Sum(Revenue*Exchange_Rate)

Sum(Revenue)*Exchange_Rate

• ###### 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

• ###### Re: Currency help based on Selection

May be this

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

• ###### Re: Currency help based on Selection

Sorry with this expresion table is not showing anything!

• ###### Re: Currency help based on Selection

Can you share a sample?

• ###### Re: Currency help based on Selection

PFA!

Thanks,

AS

• ###### 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)

• ###### 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

• ###### Re: Currency help based on Selection

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

• ###### Re: Currency help based on Selection

And it does match.... I am not sure how can I see 10613 number in the attached app

• ###### Re: Currency help based on Selection

Select Jan and Feb here than you will come to know the issue

• ###### Re: Currency help based on Selection

What is the number you see for this

=Sum([Net sales without tooling])

Do you get 14709.383?

• ###### Re: Currency help based on Selection

Yes When it for Jan Feb and March

But If u just select Jan And Feb this Sum([Net Sales...]) is 10020.29089 , so the right output should be 10613.993125233 and not 15580.91470....

Thanks,

AS

• ###### 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

• ###### Re: Currency help based on Selection

Thanks A lot Buddy!

At least i got some idea now , i just need to fix the issue if u are selecting just 1 month , as of now nothing is coming in table:

Thank u so much!

• ###### Re: Currency help based on Selection

Try this

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

• ###### Re: Currency help based on Selection

Super!