Fetch value Dynamically from an Island Table based on user selection

[DRN]

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)

)