Qlik Community

New to Qlik Sense

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

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
nisha_rai
Creator II
Creator II

Calculate Fx Movement in Qlik Sense Script

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

 

6 Replies
chrismarlow
Specialist II
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;

20201223_1.png

Cheers,

Chris.

nisha_rai
Creator II
Creator II
Author

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
Specialist II
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.

nisha_rai
Creator II
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.

chrismarlow
Specialist II
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.

 

nisha_rai
Creator II
Creator II
Author

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