# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$400 before Dec 31st: REGISTER NOW!
cancel
Showing results for
Did you mean:
Creator II

## Calculate Fx Movement in Qlik Sense Script

Hi,

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

6 Replies
Specialist II

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.

Creator II
Author

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.

Specialist II

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.

Creator II
Author

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.

Specialist II

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.

Creator II
Author

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

Tags
Community Browser