Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Novarbis
Contributor II
Contributor II

Filter dependency

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

Labels (3)
7 Replies
Akshada
Partner
Partner

Hello,

Yes. It is possible to exclude value. 

Try this..

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

Novarbis
Contributor II
Contributor II
Author

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
Partner
Partner

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
Contributor II
Contributor II
Author

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Novarbis
Contributor II
Contributor II
Author

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'.

Snag_19a44994.png

Thanks for your support!

Novarbis
Contributor II
Contributor II
Author

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!