Hi all,
My requirement is to get the Currency Rate dynamically from below table and calculate all amount fields in the dashboard based on the selected input date and currency filter.
For every Month there is entry in this table against EUR,GBP and USD values.
Currency Rates is present in a table as below:
CURR_YEARPERIOD | CURRENCY_CODE | CURRENCY_RATE | REF_CURRENCY_CODE |
May 2020 | EUR | 0.4186 | OMR |
May 2020 | EUR | 0.87327 | GBP |
May 2020 | EUR | 1 | EUR |
May 2020 | EUR | 1.0874 | USD |
May 2020 | GBP | 0.479347739 | OMR |
May 2020 | GBP | 0.87327 | EUR |
May 2020 | GBP | 1 | GBP |
May 2020 | GBP | 1.2452048 | USD |
May 2020 | USD | 0.384954938 | OMR |
May 2020 | USD | 0.803080743 | GBP |
May 2020 | USD | 1 | USD |
May 2020 | USD | 1.0874 | EUR |
Apr 2020 | EUR | 0.4235 | OMR |
Apr 2020 | EUR | 0.89425 | GBP |
Apr 2020 | EUR | 1 | EUR |
Apr 2020 | EUR | 1.0995 | USD |
This table is created as a data island using Qualify.
Dashboard has data for each period for different company( which has a different base currency).
Input Date: YEAR_PERIOD_NAME
CURR_FILTER: User has the option to select 4 Currencies – EUR,USD,GBP and BASE currency of the company.
In this table , I need the Amount in Selected currency which is Amount* Currency Rate which is fetched from above table.
COMPANY | BASE_CURRENCY | AMOUNT in BASE | Amount in Selected Currency |
0001 | EUR | 1000 | |
0002 | OMR | 20000 | |
0003 | GBP | 52000 | |
For Company 0002, when Apr 2020 and Currency EUR is selected, the Amount should be 20000*0.4235,
When May 2020 GBP is selected, it should be 20000*0.479347739,
When BASE Currency is selected it should be 20000*1
Tried below code. It picks the correct value when a Company is selected. But without company selection how can we make it work dynamically for all companies.
If(GetSelectedCount(CURR_FILTER)=0,
AGGR(MAX( {<CURRENCY_CODE={"EUR"},
CURR_YEARPERIOD={"$(=MAX(YEAR_PERIOD_NAME))"} ,
REF_CURRENCY_CODE={"$(=(BASE_CURRENCY))"}
>} CURRENCY_RATE),COMPANY)
,
AGGR(MAX( {<CURRENCY_CODE=CURR_FILTER,
CURR_YEARPERIOD={"$(=MAX(YEAR_PERIOD_NAME))"} ,
REF_CURRENCY_CODE={"$(=(BASE_CURRENCY))"}
>} CURRENCY_RATE),COMPANY)
)
