Filter dependency

[Novarbis]

Hi all, 

Is it possible to exclude a filter variable based on the chosen variable of another filter?

I have sales per country in local currency (LC) and USD, which is controlled by two filters, "Country" and "Currency".

The country filter includes an "EU" sum, for which I would like to exclude the option "LC", as the summation does not make sense.

Is there a way to automatically choose "USD" in the currency filter when "EU" is chosen in the country filter?

A simple IF(country='EU' , Currency= 'USD' , Currency) does not work, unfortunately.

Thanks a lot!

BR
Tobias

[Akshada]

Hello,

Yes. It is possible to exclude value. 

Try this..

Aggr(Only({<Currency>} Currency), Currency)

[Novarbis]

Hi Akshada, 

Thanks for your answer. 

Could you specify a little more what you're thinking?

I used your input in the expression for the Currency filter as:

=IF(Country='EU', Aggr(Only({<Currency>} Currency), Currency), Currency), 

However, it doesn't work.

Thanks!

BR
Tobias

[Akshada]

Hi Novarbis,

There is no need of "IF" condition. Just use aggr only and on selection of other filters it will show possible values of currency filter. Just copy the expression for currency filter.

[Novarbis]

Hi Akshada,

Thanks for your answer. I tried the suggested solution, and the only thing it does is to color the alternative option light grey, but it's still possible to choose the filter and see EU sales in local currencies. 

I might not have been perfectly clear, but the problem is that there exists data for each country in local currencies and USD.

So, when I select 'EU' in the country filter, it should not be possible to choose local currencies.

Thanks for your support!

Best
Tobias

[jonathandienst]

Use the following expression in the currency list box:

Aggr(Only({<Currency -= {'LC}> + <Country -= {'EU'}>} Currency), Currency)

This should show currencies, including LC, unless you select Country = 'EU'. If you select EU, the LC should not appear in the list box. 

Adjust the field names (Currency, Country) to match your model if required.

[Novarbis]

So,  below you see my filters. Think of Nordic as the sum of the other countries (DK, FI, NO and SE).

But, when Nordic is chosen it does not make sense to present data in LC (local currencies), as these are different per country and the sum does not make sense. So when Nordic is chosen, it should only be an option to choose 'USD'.

Thanks for your support!

[Novarbis]

Hi Jontydkpi

Thanks for the suggestion,

I typed it in as you wrote it (except that I added the second apostrophe to 'LC), but now the USD is the only option, no matter which country is chosen.

I think we are getting closer to solving it, though.

Thanks!