The multibox is a QlikView object that I find extremely useful because it allows you to fit selection fields into a much smaller space. At the same time, I also find it extremely annoying; the gradient looks dated and if you want your field names and values to be readable you will often have to make both columns quite wide. For these reasons I tend to not use the multibox in my applications, using a conditionally hidden drop-down filter panel instead.
Recently, I was hired to perform a health check on an application. The application made extensive use of a multibox (in a very nice and flexible way, I should add). One of the challenges my client was facing was that not every field was relevant to every user. Besides a general technical review of the application, the developers wanted to know if it was possible to create a dynamic multibox. A multibox where users could select which fields they want to show.
As I started digging in to this question, I realized there’s a much nicer way to create a multibox. After experimenting with it for a bit, I actually like this solution so much that I will probably be using it in some of my future projects.
A nicer, cleaner multibox
On the right you can see the solution that I came up with. It’s based on a pivot table object and here’s how it’s done:
- All user-selectable fields are placed in the Dimensions tab, with Enable conditional set up for each field. This allows for the visibility of the field to be toggled.
- The Label for each dimension is set to an expression that shows the name of the field (or a developer-defined label) and the current selections on that field (using the GetFieldSelections function).
- On the Expressions tab, we can enter any (temporary) expression. I used a dummy-expression of =1.
- On the Presentation tab, Dropdown Select is enabled for each dimension and Always Fully Expanded is enabled. Optionally, Wrap Header Text can be set to 2 rows, which I find gives a nicer look.
- Next, the object properties are closed and all dimensions are dragged into a vertical row.
The intermediate result should now look like the following image:
As you can see, the pivot table is showing the data area right next to the headers. The magic trick to remove this data area is to open the properties window again, navigate to the Expressions tab and replace the Definition with a single zero (“0”). Ensure that Suppress Zero-Values is checked on the Presentation tab. After closing the object properties, we are left with just the headers, which have a drop-down select on each of them; a much slicker multibox.
Why it’s nicer
Besides looking better, this multibox has a few other advantages over the original:
- The multibox is dynamic, you can let the user dynamically select which fields to display, or even use Section Access to predefine which fields are shown in the multibox. Of course, you do not need to use this option and can also create a static multibox that just looks a bit nicer.
- The user can dynamically reorder the fields by dragging and dropping them.
- The drop-down list of values is shown over the full width of the object.
- Unlike the standard multibox, this version does not only display the selected value when a single value has been selected. It will show a list of multiple, selected values (of configurable length) or will display the number of selected values, as shown in the image below:
But wait, there’s more
As pivot tables allow for both horizontal and vertical placement of columns, we can use the same solution to create a nice, horizontal drop-down selection menu.
A very slick way to display multiple selections in a relatively unobtrusive way!
What’s the catch?
There are two things to keep in mind when using this solution:
- Due to a bug, you will not be able to use the drop-down select on the bottom field in the multibox when using the AJAX client (this doesn’t affect the horizontal version). A workaround is to add an additional, blank dummy dimension.
- And, the main catch, as we’re using a pivot table, this solution will not perform well when using larger data volumes. As the data volume becomes larger, the object will require more calculation time and consume more memory. I’ve tested this solution with various data volumes and found that it was workable when data volumes were in the hundreds of thousands, but millions of rows lead to unacceptable response times. Of course, this also depends on the number of fields that you place in the multibox, more fields will mean slower calculation.
Carefully consider these caveats before deciding to implement this solution.
OK, got it, now let me try it
I’ve created a small example application so you can try out this solution for yourself. The application can be downloaded using the link below:
Download the dynamic multibox application
Of course, I look forward to hearing your feedback or potential improvements. Especially if you’ve solved this same problem in a different way. Feel free to add a comment below.
Update 2014/07/09: I’ve replace the single space expression with a zero, many thanks to Hennie Welman for pointing out this improvement.
Masters Summit Amsterdam – October 1-3, 2014
If you haven’t heard already, our traveling band of QlikView enthusiasts will be touching down in Amsterdam October 1st-3rd. I will be delivering sessions on data modeling and server administration. Hope to see you there!
More information can be found at the Masters Summit for QlikView website.
48 Comments
Hi Barry,
Thanks for sharing. I also didn’t know one can drag the dimensions on top of the expressions to get the vertical layout.
I forgot to mention, you can replace the “blank space” trick with a zero and make sure the Suppress Zero Values tick box is checked.
Hi Hennie,
Thanks, that’s even better! I’ve updated the example application and will update the blog text shortly.
Kind regards,
Barry
Hey Barry,
That custom listbox looks great! I agree completely with your view of the current listbox. Qlik should redesign it.
I wanted to download the example but it appears the link is down. Could you take a look at it? Thanks!
Mathias
Hi Mathias,
The link points to Dropbox, which some companies block. I’ve just checked and the file is still up, so maybe that’s the case at your company as well?
Cheers,
Barry
Barry, yes that must be it. I just tried at home and it works perfectely. I didn’t get the standard “not allowed in this company” message so i didn’t think in that direction. Thx again for sharing!
Very clever trick! I am surprised noone has thought of this before. 🙂
Barry,
great solution for the standard, terrible, multibox QlikView has.
Will start using it in one of my applications and will give you feedback once I will be done testing.
Marc
Thanks for this top tip, Barry.
You are a wealth of Qlik knowledge and a great inspiration for the global Qommunity. Keep doing an awesome job.
Wow, thanks Klaus. If only my old high school teachers could see me now 😉
Hi Barry
I always enjoy your postings including this one. Unfortunately the download link has generated the following error – any chance of getting the file from another source – I’m running this from home:
Error (509)
This account’s public links are generating too much traffic and have been temporarily disabled!
Best regards
Alexis from sunny Cyprus
Hi Barry,
Great post (again)! I am going to implement it on a mobile app (tablet). At least I will try 😉
Link still not working, btw.
Regards Frank
Link for download report:
Error (509)
This account’s public links are generating too much traffic and have been temporarily disabled!
Is possible to put file anywhere else?
Thank you.
Hi Barry,
This indeed is an incredible use of Pivot table; and it was a privilege to see you do this live 🙂
I must also add that I have applied your modeling suggestions on the other application – and it works superbly.
– My application size is reduced by almost 50%
– The performance of the UI has improved
– I could even get the Forecast information in the Fact table (on which we had some doubts during the workshop)
I owe you a great vote of thanks for all the knowledge you shared during the workshop, and definitely I look forward to attending more sessions of yours in future.
Thanks for the picture as well, which has made my QlikView colleagues jealous here 🙂
Regards,
Bhaskar
Hi Bhaskar,
Apologies for the delayed reply, I’m currently on holiday so not always near a computer (for a change ;)). It was nice meeting you during the QV health check, I had a good time reviewing the application and during the workshop, always very interesting to see how others approach design challenges and discuss potential solutions.
Great to hear that my suggestions were helpful, and that your colleagues liked the picture 😉
Kind regards,
Barry
Hi Barry,
Great use of the pivot table as a solution for the standard multilist box provided by Qlikview.
Is it possible to achieve ‘Hide Excluded’ in the Dropdown Select list, as available in the standard multilist box. I tried options in the presentation tab but could not make that happen, Prefer this multilist box presentation to the one provded as standard, great solution.
Kind regards,
Merzi
Hello,
I would like download to test. Can you check the link? I believe is not working.
Thanks,
Douglas
All,
I’ve moved the file to Google Drive (instead of Dropbox), the updated link should work now.
Kind regards,
Barry
Very useful and neat. Thanks for sharing.
Hi Barry,
Super like to your vision and creativity. Keep it up and thanks for a wonderful work around.
Regards,
Kaushik Solanki
Excellent use of Pivot table.
Thank you for such a out of box idea.
Regards,
Abhijit
Is there a way to include a search function? Some of the lists I deal with are very long and customers would rather type out the name than to scroll through the entire list to find it.
Looks good otherwise, and I like the ingenuity.
Hi Trey,
A search function is already included, when you open the drop-down list you can also type to search, just like in a regular listbox.
Cheers,
Barry
I need to make somethign similar to this :http://snag.gy/vWaq2.jpg as structure but i just started with qlikview and i can find anythign to help in this.
For every value as i go down by clicking then there condition for the select needed to give the sub directory like ones and so on .(i know how to make the code in sql not sure how to make it in qlikview ,any help and quidence much appreciated.
Hi Alex,
This functionality is available in the standard pivot table object.
Kind regards,
Barry
Barry,
Thanks a lot! This will be usefull to me!
Regards,
Marcelo
Hi Barry,
Very nice, impresive and creative solution! I’ve 1 suggestion: sort on state to enhance user experiance.
Thanks for shareing!
Best regards
Jelco
Hi Jelco,
The fields are already sorted on state (when you’ve made a selection). Or do you mean distinguishing between possible and excluded values? You’d probably have to write a sort expression for that, not sure how well that will perform (probably poorly).
Kind regards,
Barry
Hey Barry,
Great tutorial! One additional question, sometimes the labels do not fit and I can’t seem to find a way to widen the cells for the horizontal view.
Would there be a solution?
Thnx!
Youri
Hi Youri,
That’s disabled in the example app because I disabled the “move/size objects” checkbox on the sheet properties. When you enable it again you should be able to resize the columns.
Kind regards,
Barry
Hi,
Is there any option to ‘Hide Excluded’ in the presentation tab that is present in original multibox.
Thanks
Hi Swapnil,
You could probably come up with some calculated dimension that achieves the same effect (but without the ugly gray area masking the values). I doubt that it would perform very well though. Might have a look at this later.
Kind regards,
Barry
Hi Barry
Thank you for sharing this. Was looking for a way to disply drop down boxes horizontally as Cognos users are used to view selections like this. Makes a nice difference.
Regards
Petr
Hi,
Nice tutorial and very helpful. I tried it, but I have 2 problems:
1. I need the columns to be vertically placed, and I don’t know how to make them like that.
2. I already have a field InvoiceDate, and I want to filter by year(InvoiceDate) and month(InvoiceDate), but if I add these calculated dimensions, when I select one of them, the other looses its selection. Is there any possibility to filter the data by year and month, without adding them in the field lists?
Thank you!
I love this – Streamlined and stable menuing solution! But I am having problems with the step “all dimensions are dragged into a vertical row.” The only dragging I can get it to do is to change the order of the columns. I checked my settings and I have ‘Allow Pivoting’ enabled on all the dimensions. Any advice? I’d really like to implement this pronto.
Hi Barry
Nice solution – very simple but effective
thanks
Hi, is there any way to hide unavailable options is in list boxes?
Hey Barry,
This is so great. And it looks visually much better than original multibox. Thanks for the share.
P.S: I am not able to download the example, it says google app drive not available.
Thanks,
Angad
hi Barry,
thank you for your great tutorial.
i was able to use it, and i really love it.
but i have an issue.
if i use multibox with master detail table, then i filtered it from detail then master will automatically selected.
how i can achieve this.
in label i use “=if(IsNull(GetFieldSelections(master)) = -1, ‘Master’,GetFieldSelections(master))”.
with this script i can set ‘Master’ as label. i didn’t find function to get data that has been filtered, maybe you can get me advice for this problem.
i has asked this issue in qlikview community. https://community.qlik.com/message/760772
thank you
Hi Barry,
great solution, thank you for sharing it.
But, have you faced with the problem about long list of values in filter that takes too much space? What is the solution ?
There is a feature “limit drop-down to” in a standard multibox to avoid this problem, but I can’t find similar option to this multibox.
Brilliant idea. I wanted to have a MultiBox interface in Qlikview similar to the ones in new Qlik Sense and this one is doing it. Thank you Barry! Greetings from Turkey!
Hello Barry
excellent article. Do let me know from your experience if the ajax problem with vertical has been fixed.
also in your experience how bad is the performance hit if the number of main fact table data rows are in millions
Thanks
Dilip
Hello Barry
this is exactly what I want/need but cannot get the “•Next, the object properties are closed and all dimensions are dragged into a vertical row” step to work. Am I missing something?
Barry,
I am trying to use the Custom Multibox but when I make selection I am not getting the values in year 2015 . Can you please suggest.
Thanks
Sastry Challa
Hello, Is it possible to limit the drop down to say, 10 values rather than showing everything.
Very beautiful.
Some problem could be when a field contains many data, as well as the long list remain even another selection could short it.
I will try to work on it.
Sorry for my poor english.
Thanks
Luigi
Has anyone tried to do this in QlikView 12? We’ve used this in other apps under v11 but now in 12 I can’t get the dimensions to pivot vertically.
Barry,
Great tool, but is there a way make sure a selection is always made. Similar to “Always One Selected Value” defined in a list box.
Thanks
Jim