Multiple filters

[xingstar97]

I have multiple filters: filter1(based on field1), filter2(based on field 2), and then filter3 that users can choose either 'field1' or 'field2'. I created a pivot table. I hope: when I choose 'field1' in filter3, the rows in pivot table are values from field1, when I choose 'field2' in filter3, the rows are values from field2. Basically, filter3 determines the row of pivot table, but filter1 and filter2 can also filter data in general.

I was able to achieve this by setting rows as: if(Match(GetFieldSelections(filter3), 'field1'), field1, ) & if(Match(GetFieldSelections(filter3), 'field2'), field2, )

But for example, when I display rows by field1, and then select a specific row in the table, all other options ('field2' in this example) in filter 3 got selected automatically. I'm wondering why this happened and how to avoid it.

[Digvijay_Singh]

Can you share qvf or some pics of how your filters are appearing! words sometimes not easy to digest than a picture 🙂 and also a pic of the problem you mentioned!

[xingstar97]

Thanks for your reply! I use Qlik sense hub, I don't know how to share qvf, but here are some screenshots.

If I select cat1 in Dimension, the middle pivot table show data by cat1 (a,b,c). That's fine.

But if I continue to select b in the middle table, cat2 in Dimension is automatically selected (as well as a in the filter cat1 and s1,s2,and s3 in the filter cat2).

What's worse, if I first selected s1 in filter cat2 first, then repeat what I've described above, the number in the bottom table is wrong. The bottom table should show all items what belong to b and s1.

[marcus_sommer]

You are creating a synthetic dimension - which has always some side-effects and which isn't needed for your use-case because you could enable a field-reference by using a $-sign expansion, maybe like:

$(=minstring(filter3))

No if-loops and getfieldselections() are mandatory needed else just querying the filter-field directly respectively an associated field if the wanted filter-fieldvalues are not identically to the belonging dimension-fields. If the filter-field is set to select always only one value you could skip the min/maxstring() to ensure that always only a single value is available.

[xingstar97]

Thanks so much for your reply! 

Could you please explain a little bit more how to avoid using synthetic key in my case? I have two tables, both tables have cat1 and cat2 field. And I have two filters, cat1 and cat2. I hope when I select some value in one filter, it will apply to both tables.

Could you also explain a little bit more how to make the row of pivot table change based on a filter. That is, if the filter select cat1, the pivot table is group by cat1.

I would appreciate your help!

[marcus_sommer]

The recommended data-model in Qlik is the star-scheme with a single fact-table and n surrounding dimension-tables. This could be reached by joining/making information horizontally and/or by concatenating them vertically.

The needed measurements for it are rather simple especially for the concatenating of n fact-tables because most often have they the same data, for example sales and forecast and budget are the same as well as well orders and invoices and shipments - only the point of view is slightly different and with an extra field within the loads, like: 'sales' as Source respectively 'budgets' as Source they could be easily differentiated in dimensions, selections and/or set statements.

By applying something like this you will have just a single field for the cat and no struggles with synthetic keys or to synchronize selections.

Everything else as the above is more complicated and needs more efforts and knowledge and is usually only a painful detour.