Filter pane that filters columns based on their counts

[morenoju]

Hi guys,

I have a table in Qlik Sense with events and columns indicating the number of parties involved in it. For example, the "Event F" involved "2 cars" and "1 light truck".

How can I have a filter pane where users can make selections so the table filters accordingly?

For example, selecting "Car" and "Light Truck" would return only "Event F" because it's the only event where Cars>0 AND LightTrucks>0.

Thanks,

Juan

[ArnadoSandoval · Accepted solution]

Hi @morenoju 

This is my proposed solution:

• Each column has a weight as shown above: 32, 16, 8, 4 and 2
• If there are values for a particular event, Event-E, we need to multiply the column wight(8) by 1, the Event Weight will be 8.
• For the Event-F, the event weight is (8) * (1) + (4) * (1) = 12
• The function Sign allows us to convert any positive number into 1; and 0 for zero value.
• We added the column EventWeight at the time we load the data with this expression

32 * Sign(Injuries) + 16 * Sign(Fatalities) + 8 * Sign(Cars) + 4 * Sign(LightTrucks) + 2 * Sign(TractorTrailers) As EventWeight

Now we need to calculate the selection weight, based on these weights.

I named 'Name' the field used in my selector, containing the 5 possible options; the following expression returns the weight of the selection:

 32 * Sign(WildMatch(GetFieldSelections(Name), '*Injury*' )) +
 16 * Sign(WildMatch(GetFieldSelections(Name), '*Fatality*')) +
  8 * Sign(WildMatch(GetFieldSelections(Name), '*Cars*')) +
  4 * Sign(WildMatch(GetFieldSelections(Name), '*Light Truck*')) +
  2 * Sign(WildMatch(GetFieldSelections(Name), '*Tractor Trailer*'))

• You can assign this expression to a text box (be sure to replace the field name).
• Now, add the column EventWeight at the end of your table Event-Parties.
• Modify the expression of the Event column with this one, and un-check the Include Null Values property.

= If(GetSelectedCount(Name) = 0, Event, 
     if(EventWeight = ( 32 * Sign(WildMatch(GetFieldSelections(Name), '*Injury*' )) +
                   16 * Sign(WildMatch(GetFieldSelections(Name), '*Fatality*')) +
                    8 * Sign(WildMatch(GetFieldSelections(Name), '*Cars*')) +
                    4 * Sign(WildMatch(GetFieldSelections(Name), '*Light Truck*')) +
                    2 * Sign(WildMatch(GetFieldSelections(Name), '*Tractor Trailer*'))), Event)
     )

• Now modify the EventWeight column by adding a Show column if condition, so we ensure it will not be rendered; Qlik did not like my formula, but it worked anyway

=if(1=2, 0, 0)

 The only thing interesting with the load script is the EventWeight formula

NoConcatenate

Events:
Load *, 32 * Sign(Injuries) + 16 * Sign(Fatalities) + 8 * Sign(Cars) + 4 * Sign(LightTrucks) + 2 * Sign(TractorTrailers) As EventWeight Inline [
Event,Injuries,Fatalities,Cars,LightTrucks,TractorTrailers
A, 0,0,0,0,0
B, 0,0,0,0,0
C, 0,0,0,0,0
D, 0,0,0,0,1
E, 0,0,3,0,0
F, 0,0,2,1,0
];

NoConcatenate
Selector:
Load * Inline [
Name
Injury
Fatality
Cars
Light Truck
Tractor Trailer
];

This is the user interface used for testing

Hope this helps,

[ArnadoSandoval]

Hi @morenoju 

This is my proposed solution:

• Each column has a weight as shown above: 32, 16, 8, 4 and 2
• If there are values for a particular event, Event-E, we need to multiply the column wight(8) by 1, the Event Weight will be 8.
• For the Event-F, the event weight is (8) * (1) + (4) * (1) = 12
• The function Sign allows us to convert any positive number into 1; and 0 for zero value.
• We added the column EventWeight at the time we load the data with this expression

32 * Sign(Injuries) + 16 * Sign(Fatalities) + 8 * Sign(Cars) + 4 * Sign(LightTrucks) + 2 * Sign(TractorTrailers) As EventWeight

Now we need to calculate the selection weight, based on these weights.

I named 'Name' the field used in my selector, containing the 5 possible options; the following expression returns the weight of the selection:

 32 * Sign(WildMatch(GetFieldSelections(Name), '*Injury*' )) +
 16 * Sign(WildMatch(GetFieldSelections(Name), '*Fatality*')) +
  8 * Sign(WildMatch(GetFieldSelections(Name), '*Cars*')) +
  4 * Sign(WildMatch(GetFieldSelections(Name), '*Light Truck*')) +
  2 * Sign(WildMatch(GetFieldSelections(Name), '*Tractor Trailer*'))

• You can assign this expression to a text box (be sure to replace the field name).
• Now, add the column EventWeight at the end of your table Event-Parties.
• Modify the expression of the Event column with this one, and un-check the Include Null Values property.

= If(GetSelectedCount(Name) = 0, Event, 
     if(EventWeight = ( 32 * Sign(WildMatch(GetFieldSelections(Name), '*Injury*' )) +
                   16 * Sign(WildMatch(GetFieldSelections(Name), '*Fatality*')) +
                    8 * Sign(WildMatch(GetFieldSelections(Name), '*Cars*')) +
                    4 * Sign(WildMatch(GetFieldSelections(Name), '*Light Truck*')) +
                    2 * Sign(WildMatch(GetFieldSelections(Name), '*Tractor Trailer*'))), Event)
     )

• Now modify the EventWeight column by adding a Show column if condition, so we ensure it will not be rendered; Qlik did not like my formula, but it worked anyway

=if(1=2, 0, 0)

 The only thing interesting with the load script is the EventWeight formula

NoConcatenate

Events:
Load *, 32 * Sign(Injuries) + 16 * Sign(Fatalities) + 8 * Sign(Cars) + 4 * Sign(LightTrucks) + 2 * Sign(TractorTrailers) As EventWeight Inline [
Event,Injuries,Fatalities,Cars,LightTrucks,TractorTrailers
A, 0,0,0,0,0
B, 0,0,0,0,0
C, 0,0,0,0,0
D, 0,0,0,0,1
E, 0,0,3,0,0
F, 0,0,2,1,0
];

NoConcatenate
Selector:
Load * Inline [
Name
Injury
Fatality
Cars
Light Truck
Tractor Trailer
];

This is the user interface used for testing

Hope this helps,

[ArnadoSandoval]

@morenoju 

I worked for about two to three hours in your reply, but this chat-room refuse to post it.

I will try in a while

Best regards,

[morenoju]

Hi @ArnadoSandoval ,

There must be some glitch in the Qlik Community, because I received your solution many times.

In any case, it's amazing the level of detail you've offered. Your solution works great.

Thank you!!

[ArnadoSandoval]

@morenoju 

I just noticed that Qlik kept all my failed replies attempt for hours, then posted all of them, Oh well; I was blamed of spamming the site 😮

Anyway, I am glad the solution worked !!! 

[morenoju]

Haha no worries @ArnadoSandoval . If you want, you can remove the extra responses at any time.

Anyway, I have a question you may know the answer for. I'm trying to add a similar behavior to many KPI and charts I have next to the table. My plan was to create a variable such as:

vEventWeight

=32 * Sign(WildMatch(GetFieldSelections(Name), '*Injury*' )) +
16 * Sign(WildMatch(GetFieldSelections(Name), '*Fatality*')) +
8 * Sign(WildMatch(GetFieldSelections(Name), '*Car*')) +
4 * Sign(WildMatch(GetFieldSelections(Name), '*Light Truck*')) +
2 * Sign(WildMatch(GetFieldSelections(Name), '*Tractor Trailer*'))

And then use it in Set Analysis like this:

Count({<EventWeight='$(vEventWeight)'>}Event)

The only problem I see is that if I don't make any selection on Name, the EventWeight is null and 

Count({<EventWeight=''>}Event)

returns 0 instead of the total of events regardless their weight.

I've tried many things in the variable definition, because Count({<EventWeight=>}Event) (without the apostrophes) would provide the results I need.

Do you have any idea that could help?

[ArnadoSandoval]

Hi @morenoju 

Actually this is a very interesting and valid scenario,  when introducing the SET analysis the expression looks like this:

Count({<EventWeight={ } >}Event)

Between the curly brackets the elements of the set, each time we make a selection, the expression become:

Count(<EventWeight={56}> Event)

After selecting: Cars, Fatality, Injury; it works as expected, but then we select nothing, we actually want to see all the possible combinations, like this:

'62, 60, 58, 56, 54, 52, 50, 48, 46, 44, 42, 40, 38, 36, 34, 32, 30, 28, 26, 24, 22, 20, 18, 16, 14, 12, 10, 8, 6, 4, 2, 0'

I created a vEventWeight variable defining all the elements in the set when no selections are made:

I created a Text control to monitor the resulting set analysis expression, like below

The text control expression is this:

='{<EventWeight={$(=$(vEventWeight))}>}'

This was a way to visually check the set analysis in use, then the expression in the KPI become unorthodox, like this:

Count( $(='{<EventWeight={$(=$(vEventWeight))}>}') Event)

It is like cheating, but it worked, I stop trying at this point, the next test was to replace the KPI expression with something like this:

Count( {<EventWeight={ $(=$(vEventWeight)) }>} Event)

Actually, I tried the last one and it works too !!!

This is the script expression for the vEventWeight variable

Set vEventWeight=[If(GetSelectedCount(Name) = 0, '62, 60, 58, 56, 54, 52, 50, 48, 46, 44, 42, 40, 38, 36, 34, 32, 30, 28, 26, 24, 22, 20, 18, 16, 14, 12, 10, 8, 6, 4, 2, 0', 
                   32 * Sign(WildMatch(GetFieldSelections(Name), '*Injury*' )) +
                   16 * Sign(WildMatch(GetFieldSelections(Name), '*Fatality*')) +
                    8 * Sign(WildMatch(GetFieldSelections(Name), '*Cars*')) +
                    4 * Sign(WildMatch(GetFieldSelections(Name), '*Light Truck*')) +
                    2 * Sign(WildMatch(GetFieldSelections(Name), '*Tractor Trailer*')))]; 

I wish for a better way to do this, but it works,

Hope this helps,

[morenoju]

Amazing, @ArnadoSandoval , very creative response!

I never expected a solution like this but... it does work! By the way, the syntaxis is Qlik for variables is tricky... 

Thanks!

[ArnadoSandoval]

@morenoju 

We can do a lot of things with variables, certainly Qlik does a great job with them, although, SET analysis syntax is tricky indeed.

Regards,