Qlik Sense: Ignoring filter in the Dimension but considering afterwards

[dawu1402]

Hello fellows,

I have following problem and have not found  an answer I, as a newbie, could understand, so far:

 

The basis for my example is the table above. I want to show in a table all Areas, irrespective of any filter. But additionally it should be possible, to filter for Salespersons.  For example I want to filter for Salesperson A, but still show all areas upfront. The result-table should look like this:

Does anybody have an idea how to solve this issue or did already solve this issue and I have not found the right thread so far? I hope the question is correct in this form and place.

Thanks in advance for your help.

[rogerpegler · Accepted solution]

What you want to do is to override what Qlik is trying to make easy for you! 

One way is to have another measure that has a relationship with all the Areas irrespective of Salesperson.  If there's not something else that you want to add anyway, you could have :

Sum({1}1)

Make sure that show Nulls is enabled for the dimension and show zeros is enabled in the Add-Ons properties of the table.

You can improve the presentation by playing with font colors and background colors.

[Mauritz_SA]

Hi there

You can modify the method shown in this post (https://community.qlik.com/t5/QlikView-Documents/Show-all-values-in-Pivot-Straight-Table-irrespectiv...) a little bit.

In you second table's Salesperson measure you can use If(Count({1<Salesperson>}Salesperson)=Count(Salesperson),Count(Salesperson),Count(Salesperson))

I tested on my side and it seems to work.

Good luck.

Mauritz

[dawu1402]

Thanks, that is what I was looking for!

[dawu1402]

After that works perfectly within the second table, my second problem occurs. I want to visualize that in a map. Every Region with Sales in it in green, others in red. This easily works wit if clauses. But the map also applies to the filter. How can I ignore that as well in the map?

Thanks in advance for any help.

[rogerpegler]

Do you mean that the map only shows the areas that have data?

Is it just a matter of enabling "Show Excluded" in the Area Layer?

[dawu1402]

In fact no, rather the other way. The areas are combined with countries. Generally, all countries, that are in these areas should be red. If there is data in it for the region, they should be green. My issue is the first point. When a selection is made, only the countries occur, that account to this selection in green. But all the others should then be red (so a region, which as applied, but not affected due to the selection). I hope, this is comprehensive and maybe you can help me once more?;)

[dawu1402]

I solved this issue by adding a background layer with other terms. Never the less thank you for your support. Great to have such a community as help station!