Can I put a hidden/permanent filter on a chart?

[BarryPollock]

Hi everyone,

Let assume I have a table that contains the following fields:

Account / Country / Region/ Sales

I want to make a bar graph that shows the Country on the X axis, and the total sales on the Y axis.  I want to show only countries that are in Region = Europe and I don't want the user to be able to change the Region on this chart.  

Can I do this?

If I do this, can I have a visible filter on the sheet that will allow the user to select specific countries in Europe?

If so, how is all of this done? 

Thanks!
- Barry

[Lisa_P · Accepted solution]

Hi Barry,

The answer is Set Analysis.

Inside your measure Sum(Sales), you would insert {<Region={Europe}>} and it would filter out only the Countries in Europe. This still allows you to create a filter pane for Country to make selections.

So final measure would be:

Sum({<Region={Europe}>} Sales)

[Saryk]

Another option, if you don't want to have to define the set analysis formula for every measure, is set it in the dimension expression : 

=if(Region='Europe', Country)

That is permanent.

[BarryPollock]

Thanks!  Is there a way I can select multiple regions?

Also, is there a good resource for teaching how to write these expressions?  The syntax is very different than what I am used to.

[Lisa_P]

Google 'Set Analysis' and there should be many links.

Here is an introductory video:

https://www.youtube.com/watch?v=YMQJnKMkfxg

Also here is another great resource, the Set Analysis wizard:

http://tools.qlikblog.at/

[Saryk]

Using set analysis, you can select multiple regions with this syntax (example) :

Sum({<Region={Europe,North America,Asia}>} Sales)

 It has certainly been posted, but this link and the links inside this page are explanations of set analysis and can be used as reference. 

As many things with Qlik, the syntax is very inconsistent with the rest, and it is still always confusing. Good luck !

Cheers !

[BarryPollock]

Hi again,

I've run into another problem.  My region names are EMEA, AMERICAS, and Asia, Pacific.

So the expression is Sum({<Region={EMEA,AMERICAS,ASIA, PACIFIC}>} Sales)

Of course, the problem is that it thinks that Asia and Pacific are two distinct regions separated by a comma, when the name is literally "Asia, Pacific."

How can I handle the comma in the region name?

Thanks!

[Lisa_P]

Use single quotes:
Sum({<Region={EMEA,AMERICAS,'ASIA, PACIFIC'}>} Sales)

[BarryPollock]

Ahh, of course!

I actually tried that, but it didn't work.  It turned out that I was placing a space after the comma when there shouldn't be one.

Thanks again!

[Spartan27215]

In your example, you use Europe, which is a known value, what if the value is not known but simply the most current one available?

Example: Different customers have different reporting period, and can have multiple reporting periods they are arranged by a Period Type (CY,- Calendar Year FY - Fiscal Year, BY - Benefit Year). The report period type is selected as the filter say CY which contains 2018, 2019, 2020, 2021, a different user may select BY which has 2018, 2019, 2020. 

What I want the charts to reflect is the last one in the series, i.e. 2021 in the 1st example and 2020 in the second.