topic Currency help based on Selection in QlikView

Currency help based on Selection

amit_saini — Tue, 11 Jul 2017 12:25:34 GMT

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.

I'm not sure ,how this can be achieved , please help!

My expression is = Sum(Revenue)*Exchange_Rate

Regards,

Amit

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 12:27:31 GMT

May be try this

Sum(Revenue*Exchange_Rate)

Instead of this

Sum(Revenue)*Exchange_Rate

Re: Currency help based on Selection

amit_saini — Tue, 11 Jul 2017 14:28:16 GMT

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,

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 14:43:14 GMT

May be this

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

Re: Currency help based on Selection

amit_saini — Tue, 11 Jul 2017 14:48:37 GMT

Sorry with this expresion table is not showing anything!

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 14:51:35 GMT

Can you share a sample?

Re: Currency help based on Selection

amit_saini — Tue, 11 Jul 2017 14:57:50 GMT

PFA!

Thanks,

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 15:03:28 GMT

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

amit_saini — Tue, 11 Jul 2017 15:14:07 GMT

See this:

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

Thanks,

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 15:16:40 GMT

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

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 15:17:32 GMT

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

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 15:19:26 GMT

What is the number you see for this

=Sum([Net sales without tooling])

Do you get 14709.383?

Re: Currency help based on Selection

amit_saini — Tue, 11 Jul 2017 15:37:50 GMT

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,

Re: Currency help based on Selection

sunny_talwar — Tue, 11 Jul 2017 15:43:20 GMT

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

amit_saini — Tue, 11 Jul 2017 15:45:14 GMT

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

Re: Currency help based on Selection

amit_saini — Tue, 11 Jul 2017 15:50:37 GMT

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

sunny_talwar — Tue, 11 Jul 2017 15:52:53 GMT

Try this

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

Re: Currency help based on Selection

amit_saini — Tue, 11 Jul 2017 15:58:11 GMT

Super!