Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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.
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.
Thanks Chris, It's working fine for table 1, but not working for table 2.