I am new to QlikSense and have an urgent requirement to implement .
Requirement :-
I have below data uploaded from 3 excel files in qliksense data table
Scenario
Year
Month
Period
Division
Reporting Currency
Amount in Currency
Amount in €
Budget 2020
2020
Dec
12
P
EUR
40
Budget 2020
2020
Dec
12
P
EUR
50
Budget 2020
2020
Dec
12
P
EUR
10
Budget 2020
2020
Dec
12
C
KRW
140,462
Budget 2020
2020
Dec
12
C
KRW
75,187
Budget 2020
2020
Dec
12
C
USD
2,500
Budget 2020
2020
Dec
12
C
USD
166
Budget 2020
2020
Dec
12
C
EUR
300
Budget 2021
2021
Dec
12
P
EUR
40
Budget 2021
2021
Dec
12
P
EUR
50
Budget 2021
2021
Dec
12
P
EUR
10
Below is exchange_rate mapping file which is to be used to calculate "Amount in €" for the above data set.
Scenario Type
Scenario Year
Scenario
Year
Month
Period
View
Reporting Currency
Exchange Rate
Budget
2020
Budget 2021
2021
Dec
12
YTD
EUR
1.00000
Budget
2020
Budget 2021
2021
Dec
12
YTD
USD
1.15000
Budget
2019
Budget 2020
2020
Dec
12
YTD
KRW
1340.00000
Budget
2019
Budget 2020
2020
Dec
12
YTD
USD
1.15000
Actual
2019
Actual 2019
2019
Dec
12
YTD
USD
1.12000
Actual
2019
Actual 2019
2019
Dec
12
YTD
KRW
1304.89900
There is a dropdown in Qliksense worksheet with 3 options as below :-
Budget 2020
Budget 2019
Actuals 2019
Now the requirement is -> When a user selects Actuals 2019 from dropdown, it should extract exchange rate of Scenario Type='Actuals 2019 ' and reporting currency mapped from data table and use the extracted exchange rate to calculate Amount in € and display in Data table field 'Amount in €'.
Similarly Amount in € should be calculated for other dropdown options also.
For example : User selects Actuals 2019 from dropdown, now if we want to calculate Amount in € for currency 'KRW' then in exchange_rate mapping file it has 2 records one for 'Budget 2020' and other for 'Actual 2019', so I have to take 'Actual 2019' -KRW- 1304.899 for calculating and displaying 'Amount in €' in data table.
