Hierarchical sheets navigation in QlikView ("breadcrumbs")

Ever wondered how you can get website style navigation in QlikView?

By that I mean you start with a home page, and a menu, and as you whittle down the categories to get what you want they stick on the top of a screen with an arrow to show that you've been there.  (If anyone can think of a better description then let me know...!)

edit: I've just been reminded it's also called breadcrumbs, thanks speros!

Examples:


Home>Our company>Careers>Vacancies>Vacancy search results

 

Someone asked me for this today. The Dashboard that I am doing for them will potentially have lots of tabs, and they might want to add and change it regularly! So I spent a while trying to get a solution that was as systematic as possible.

This is also a handy solution for applications that are going to be used on phones or tablets, as space is even more at a premium and I'd prefer this to having to scroll horizontally. (In most cases).

Oh, and I'm not allowed to use macros...


See my example here:
https://docs.google.com/file/d/0B3xmX3uIr8YeczB6NUtpMlIwdTA/edit?usp=sharing

Step 1: Define the relationships in a parent / child table

 My solution is to store the hierarchy in a spreadsheet as parent/child nodes, load this into the dashboards then use as many conditional options as possible to get it to view nicely.

First, you need a master document with the relationships of the sheets in. I find it easier to create this first. The example spreasheet in the link below comprises 3 columns: Parent Sheet, Child Sheet, and Childsheet Name (which is exactly the same as childsheet but this could be created in the script)

Then you just need to list each relationship in the table with the higher level being the "parent" and the lower level being the "child". EG Main>User>Time Charts would be listed

Parent	Child
Main	User
User	Time Charts

 Step 2: Get a table that tells you which sheets to show and which not to show

This the table that we want to end up with:



It's a list of all sheets, the sheets that should be visible when you click on it and a column that marks whether those visible sheets are ones that are "new" ie to be selected as the next options.

This is easier to understand if you select a current sheet:



Here the  sheet selected is Inpatients, and the navigation would look something like this:

Main>Corporate>Inpatients   with the option to choose IP1, IP2, IP3, IP4

How do we get there?

 I'll go into  minimal detail, as I feel like I could write too much on hierarchies!

In the script, the HierarchyBelongsTo() function takes the nodes and turns them into a table with the sheets listed as the "child" column and then ALL the ancestors (so the parents, "grand parents" etc listed) in another. Here the child column is really the "current sheet" and the ancestors are the visible sheets, the ones that come before it. It also saves a "depth" how far that sheet is from the other table.

To get the "optional ones" I just concatenate from that table using the depth field to select the next sheets on.

 Step 3: Create your sheets!

The easiest way I found to do this was to create 1 sheet first, then copy it lots of times and edit those sheets.

To get the sheets to show conditionally:

•  Give your sheet the name that the hierarchy document refers to: SHT_Corporate
• Create a varible in the document called vCurrentSheet that gets the currently selected sheet name: =replace(GetActiveSheetId(),'Document\','')
• Add an  on activate action to the sheet that selects in the field "current sheet" which selects the current sheet variable =vCurrentSheet
• Add a conditional show that tests if the sheet is in the visible sheets selected =count({<[Visible Sheets]={"SHT_Corporate"}>}[Visible Sheets])>0

To colour the sheets if they are "new options":

• Set a calculated tab colour if the "nexttab column" relating to that sheet is 1: =if(sum({<[Visible Sheets]={"SHT_Corporate"}>}[NextTab])>0,vTabColour)
• Here I've created a variable incase I change my mind later (this happens a lot..!)

To create a ">" symbol that appears after the sheet I've added an if statement that tests the "depth" of the sheet compared to the one currently selected. Any depth >0 will be an ancestor, and will warrant your arrow symbol:
='Corporate'&if(sum({<[Visible Sheets]={"SHT_Corporate"}>}DepthDiff)>0, '>','')  


So there we go. Much easier to figure out if you have a play in the document.

Still not ideal, but it keeps me organised! Does anyone have any other ideas out there?

Welcoming your questions,

Erica :)


Link https://docs.google.com/file/d/0B3xmX3uIr8YeczB6NUtpMlIwdTA/edit?usp=sharing