Question regarding comparison of two time periods. I have no idea on how to code this in Qlik. Any idea is welcome.
The user can freely select what is period 1 and period 2, example, Q1 15 and Q1 16 or Q1 16 and Q2 16 or June 16 and July 16
The analysis is in both constant dollars and local currency
The first period dollar amount must be recomputed using the exchange rate of the second period
Exchange rate of second period is Price in dollar / price in local currency
This exchange rate can cover one or more months, including months which are in the future (Orders)
The dollar price of the first period is recomputed using the Price local currency Period 1 * exchange rate of period 2
The dollar amount of period 1 is computed using units of period 1 * recomputed Dollar price period 1
This works when the local currency code is displayed however I need to present the straight table using different levels of aggregations and I need to show a total.
The problem is that the exchange rate is then computed using the sum of different currencies and this is wrong
I would need something that computes (or materialize) the individual values and then the aggregations (or the total) should sum these individual values instead of using an exchange rate.
I do not think I can compute these values in the loading script since the user may select any period for period 1 and period 2
