Calculate Fx Movement in Qlik Sense Script

[nisha_rai]

Hi,

Please help me resolve the below query in Qlik sense.

i want to calculate the Fx Movement in Qliksense Script or front End.

for example:

Date CurrencyCode  Currency

1/1/2020 AED 0.223

1/1/2020 AUD 0.324

1/1/2020 EUR 0.217

1/2/2020 AED 0.213

1/2/2020 AUD 0.321

1/2/2020 EUR 0.214

1/3/2020 AED 0.212

1/3/2020 AUD 0.312

1/3/2020 EUR 0.224

want to calculate the below logic in Script/Front end

if March and Feb selected the Fx movement will be

=>Currency Feb/Currency March

If March and Jan selected the Fx Movement will be

=>Currency Jan/Currency March

If Jan and Feb selected the Fx Movement will be

=>Currency Jan/Currency Feb

want the calculate the above 3 calculation in single column.

Regards,

Nisha

[chrismarlow]

Hi,

I am not sure you would do this in a script.

In the front end try something like Sum({<Date={"$(=Date(Min(Date),'D/M/YYYY'))"}>}Currency)/Sum({<Date={"$(=Date(Max(Date),'D/M/YYYY'))"}>}Currency), so parts of this shown below;

Cheers,

Chris.

[nisha_rai]

Thanks for your response Chris.

But I can't take sum , because we need to show fx movement by Month  only.

Currency should not be the part of table in front end.

[chrismarlow]

Hi,

You could replace Sum with Only in what I have suggested as the aggregation in this case is only ever over one value, but I am not really sure I follow your point on currency, so maybe I am not following what you mean.

Perhaps you could share a mock up of what you trying to get to from the numbers you have provided.

Cheers,

Chris.

[nisha_rai]

Hi Chris,

I tried Only function ,but it works only when we have currency in the table or when we select one currency at a time.

please have a look at the attachment of input and expected output tables.

[chrismarlow]

Hi,

You are summing sums, so you need to use AGGR, so try;

Sum(Aggr(Only({<Date={"$(=Date(Min(Date),'D/M/YYYY'))"}>}Currency)/Only({<Date={"$(=Date(Max(Date),'D/M/YYYY'))"}>}Currency),CurrencyCode))

In a chart this will just give you a single value, which will depend on your selections. You would need to do something different if your chart is supposed to have 3 rows like your table 2 on the sheet. The numbers in your sheet don't match the initial post, so I've not shared a screen shot.

Cheers,

Chris.

[nisha_rai]

Thanks Chris, It's working fine for table 1, but not working for table 2.