Apply filter to multiple columns

[MALKITOM]

Hello,

I have a issue with applying filter on chart sheet to multiple columns. 

More or less, I have following table when loading:

Col 1	Col 2	Col 3
A	B	C
C	A	B
C	B
A	C
B	C	A

And at the end I would like to have filter:

A
B
C

which would be able to apply to Col 1, Col 2, Col 3 (by OR operator). So whenever, value A was chosen from filter, rows that contains value A in Col 1 or Col 2 or Col 3 will be displayed. A,B,C are text (string) values. Is there any possibility to achieve what I want?

Regards,
Tom

[Or]

Assuming your filter is a field named Filter, you would then write this into your formula, e.g.

Sum({< Col1=p(Filter) + Col2=p(Filter) + Col3=p(Filter) }> SomeOtherField)

[MALKITOM]

Hello,

Will this also apply for sheet? I mean that I would like to have those A,B,C in filter pane (easy to do as to drag and drop required column from main table) and filter pane's selections should interacts on what is displayed in tables, charts other visualizations on same sheet.

Where then should I put this formula?

[Or]

This formula doesn't care where you made your selections - Smart Search, Selections screen, filter pane, click within an object... it just looks at the state of selections for your app.

This formula goes in your actual object (chart, table, or whatever else). Note that you may need something other than sum(SomeOtherFields), but the important part is the set analysis part encased by {< >} which should be copied into any aggregation functions (sum, count, only, etc) you want to work this way.

[marcus_sommer · Accepted solution]

Your data-structure is a crosstable which is rather seldom useful or beneficial in Qlik at least not within the UI because those multiple fields couldn't be selected/aggregated in a common way. Therefore I suggest to resolve this structure with The Crosstable Load - Qlik Community - 1468083.

- Marcus

[MALKITOM]

Hi,

I have tried to adjust this formula to my requirements but I am getting error: "Error in set modifier expression".

Count(DISTINCT {<[Col 1] = p(Filter) + [Col 2] = p(Filter) + [Col3] = p(Filter)>} ColumnToCount)

('Filter' is a name of the field from filter pane, filter values as created as the distinct values from table above)

Still, could explain me what 'p' stands for?

Regards,

Tom

[Or]

p() within set analysis stands for "possible" - that is, the possible values of the field Filter. If you've made a selection, this will be the selected vales. If you have not made a selection, this will be the white-background values (that is, the ones you can select). You may need to tweak the formula to make it work, I obviously couldn't test it without access to your data...

Marcus may be correct (see his comment) in that the issue may be with the underlying data structure, so that may be an avenue to pursue as well.

[MALKITOM · Accepted solution]

Hello,

It seems that I have manage to user Crosstable to achieve what I wanted to achieve. Thank you very much!

Regards,

Tom