One filter pane for two columns via set analysis

[a_kosarev]

Hi everyone! 

I'm currently trying to apply one filter pane to two columns of a pivot table with similar date values. 

Here is what I have come up with so far: 

count(
{1<[Field1.autoCalendar.YearMonth] = {"$(=GetFieldSelections([Field2.autoCalendar.YearMonth]))"}>}
[Field1])

So, if I set a filter on Field two, it will automatically count the filter chosen values from Field1.

Now, the problem I have is that if I try to choose more than one value on the filter pane, GetFieldSelections returns a string like "value1, value2", and obviously my functions crashes down. 

I understand what causes the problem, but can't think of any way around. It would make sense to replace the "=" with "in" to make a python-like structure, but qlik sense doesn't have the in-operator. 

Could you please give me some ideas? 

[jaibau1993 · Accepted solution]

Hi!

You have something like that "in" you are looking for. In a set expression you can use

{<Country = {'Spain', 'F*'}>}

Which means Country = 'Spain' or Country like 'F*'. You need a list of values separated by a comma. Said so and following your approach to the problem, you can use

Sum({<Date1 = {'$(=GetFieldSelections(Date2, chr(39) & ',' & chr(39)))'}>} Amount)

BUT the following expression is simpler and does exacly the same 

Sum({<Date1 = $::Date2>} Amount)

The set modifier of the above expression means Date1 = "What is selected in the field Date2 in the state $" . 

Finally, note that if you select nothing both expressions above will rise a 0.

Regards,

Jaime.

[a_kosarev]

Hi, Jaibau

To be honest, I coudn't get the shorter one going, but you first suggestion works just fine! I was almost there 😃

Situations when nothing is selected can be easily avoided with an if statement. 

Thank you so much!