Qlikview: Funnel chart to display current selection

[#1]

Hi Team,

Thanks to Qlikview we have an excellent product to surf our complex cancer data.

One thing we need is a visual way to see the filters applied and the effect it had. Let me give a simple example: let's say a researcher starts with the complete dataset of 1000 patients and looks for following patients:

(1) Having Colon Cancer

(2) Age of 50 years or less

(3) having a certain genomic  precondition

Currently we have these filters available in a multibox and the savvy person can find the right filters, use them and see them reflected in the Current Selection box.

What we need is to make this exercise visual for rest of the users. For example they could select a bookmark which has these filters selected and  a funnel diagram appears that shows how many patients are available after each level of filter. One step beyond would be to be able to change the parameters chosen in those filters from the funnel visual. (Please see the attached attempt at a visual)

In other words, is there a way to use CurrentSelections to create a funnel diagram dynamically based on the filters chosen?

We have been able to create such adhoc features using calculated expressions to create x and Y axis dynamically etc. But I am stumped on this request.

HELP!

Thanks,

Amit

[stevedark]

Hi Amit,

The simplest view of selection vs. whole with just selected vs. non selected could be achieved by stacking two expressions.  If you have a PatientCount field on your Patients table (which I would recommend) you could do:

sum(PatientCount)

and

sum({1}PatientCount) - sum(PatientCount)

To give you both the selected number and the non selected number.

Applying each of the selections in turn would be more complex.  The only way I can think is to look at a number of past selections in turn:

sum({$}PatientCount)

sum({$1}PatientCount)

sum({$3}PatientCount)

etc. etc.

So, this would give you the current selection, what it was before the previous selection, what it was before that etc.

The tricky bit here (which I have tried quickly to achieve with an Action, but it didn't work out - a macro might) is initialising things such that when you start with the full cohort only the current selection has a value and the others are zero.  It should be possible to achieve this though by having buttons to clear selections and variables to track how many selections have been made.

Beyond this, you are getting into parsing the output of GetCurrentSelections() and interpreting that.  This will be tricky in itself - not least as the selections will not necessarily appear in the order they are made.

A further thought is persisting the count at each selection to a variable with triggers (OnAnySelect) so, on the trigger if Sum(PatientCount) = sum({$}PatientCount) you know you have no selections in play - so can initialise, then on each subsequent selection you could persist the value.

Sorry, I have given you far more questions than answers here - but hopefully it points you in the right direction.

Final point, is that I would personally beware of funnel charts - as it is not clear to the person interpreting the chart whether it is the volume of the section or the spread of the lines that is giving the value (and indeed QlikView has an arbitrary check box to toggle the two).  A stacked bar may be the better visualisation here.

Hope that helps.  It's fantastic work that you are doing.

Steve

[blaise]

The problem is that GetCurrentSelections() returns the fields selected in alphabethical order, not in the order they were selected, it would really love a "sort field parameter" in GetCurrentSelections() and also in the Currect Selection box.

The other issue is that you cannot tell the user what {$x} means, only that its x steps back.

I see no way in solving this req. without the use of actions, either user initiated (good for performance but not that user-friendly) or onAnySelect (bad for performance, more user-friendly).

[khan_imran]

You can lock on of the selection. then do another selection and lock it again. You can perform this activity many times in similar chart.

[#5]

Thanks much all! I am working with these suggestions and also hoping to get some professional help from Qlik directly. Please do post any other insights that may come. I will keep the group posted with what I uncover.

Amit

[stevedark]

Hi Amit,

Good luck with this.

The more I think about it, the more I think that using the OnAnySelect trigger is going to be your route to doing this.

You could create a set of variables, for however many levels you want to go down, i.e.

Set1

Set2

Set3

etc.

Then, your OnAnySelect trigger would need to pick up on the current state to set each variable.  So, the code to set the value for Set1 would be something like:

=if(sum(PatientCount) = sum({1}PatientCount), sum(PatientCount), Set1)

This initialises the value when all selections are cleared, or stays the same if not.  Set1 will always contain the full set of patients.  In fact - the action is slightly redundant for this reason.

The code to set Set2 would be:

=if(sum(PatientCount) = sum({1}PatientCount), 0,

if(Set2 = 0, sum(PatientCount), Set2))

This will reset the value for Set2 to zero on Clear Selections, if it is the first selections (and Set2 will be zero at this point) the current number is persisted to Set2, otherwise it is unchanged.

The code for the remaining Sets will be identical to set to, just with every occurrence of 2 changed to n.

This should give you all the values to plumb into your chart, the expressions would just be =Set1 etc.

This will capture the numbers, but it won't capture the selections.  If you need to pull this out you are down to clever parsing of GetCurrentSelections again.  You would need to persist selections to variables at each selection, and work out what text is new at each level.

Good luck!

Steve

[Dalton_Ruer]

Amit - I've attached a QVW that demonstrates how to setup a variable that would maintain the selection order (first they selected something from Field5, then they selected something from Field3 etc)

To see the code for the triggers you would look in Documents\Triggers and then scroll to the VAR1, VAR2 and VAR3 fields. You will see I added Selection and Change triggers for all 3 fields. As you click and choose values you will see that it builds and maintains the selection order.

BTW - I am a part of the Qlik Healthcare team and would love to chat with you about your use case and help you in any way I can to make it ROCK!!!!!

stevedark‌ - Thanks for posting this to Twitter. Very interesting use case and your previous replies are stellar (as always.) Really made me think through "how in the world it could be done."

Thanks,

[#8]

Thanks Steve and Dalton: Appreciate all the help and thoughtfulness. Whatever I get to by early next week, I will share it and seek further guidance if needed. But in the meantime please keep those inputs coming!

[stevedark]

Hi Dalton,

Glad you saw the tweet and pleased you created a workable solution.

If I can offer further assistance please give a shout back on this thread.

This is exactly the sort of thing that Qlik Community should be about.

Steve

[padmanabhv]

Revisiting this Q. Did you ever figure out a solution ? 
Thank you !