Building a nicer (dynamic) multibox, without extensions

By Barry Harmsen

Building a nicer, dynamic multibox in QlikViewThe 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 QlikView multiboxA 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:

Subresult

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:

A nicer QlikView multibox, with selections

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.

Horizontal drop-down selection bar

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 for QlikView - Amsterdam

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.

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

49 Comments

  • 1
    Hennie Welman
    July 9, 2014 - 10:38 | Permalink

    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.

  • 2
    Hennie Welman
    July 9, 2014 - 10:41 | Permalink

    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.

    • 3
      July 9, 2014 - 13:48 | Permalink

      Hi Hennie,

      Thanks, that’s even better! I’ve updated the example application and will update the blog text shortly.

      Kind regards,
      Barry

  • 4
    Mathias Vanden Auweele
    July 9, 2014 - 10:45 | Permalink

    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

    • 5
      July 10, 2014 - 23:17 | Permalink

      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

      • 6
        Mathias Vanden Auweele
        July 10, 2014 - 23:29 | Permalink

        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!

  • 7
    July 9, 2014 - 11:19 | Permalink

    Very clever trick! I am surprised noone has thought of this before. 🙂

  • 8
    Marc Algera
    July 9, 2014 - 11:28 | Permalink

    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

  • 9
    Klaus Feldam
    July 10, 2014 - 03:42 | Permalink

    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.

    • 10
      July 10, 2014 - 23:16 | Permalink

      Wow, thanks Klaus. If only my old high school teachers could see me now 😉

  • 11
    July 11, 2014 - 22:31 | Permalink

    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

  • 12
    Frank
    July 14, 2014 - 10:55 | Permalink

    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

  • 13
    July 14, 2014 - 12:50 | Permalink

    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.

  • 14
    Bhaskar Sharma
    July 14, 2014 - 13:16 | Permalink

    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

    • 15
      July 21, 2014 - 17:00 | Permalink

      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

  • 16
    Merzi
    July 14, 2014 - 15:49 | Permalink

    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

  • 17
    Douglas Junkherr
    July 15, 2014 - 15:29 | Permalink

    Hello,

    I would like download to test. Can you check the link? I believe is not working.

    Thanks,
    Douglas

  • 18
    July 15, 2014 - 18:14 | Permalink

    All,

    I’ve moved the file to Google Drive (instead of Dropbox), the updated link should work now.

    Kind regards,
    Barry

  • 19
    Nakul
    July 16, 2014 - 15:53 | Permalink

    Very useful and neat. Thanks for sharing.

  • 20
    Kaushik
    July 17, 2014 - 07:32 | Permalink

    Hi Barry,

    Super like to your vision and creativity. Keep it up and thanks for a wonderful work around.

    Regards,
    Kaushik Solanki

  • 21
    Abhijit
    July 23, 2014 - 08:31 | Permalink

    Excellent use of Pivot table.
    Thank you for such a out of box idea.

    Regards,
    Abhijit

  • 22
    Trey
    August 12, 2014 - 20:57 | Permalink

    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.

    • 23
      August 13, 2014 - 10:10 | Permalink

      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

  • 24
    Alex
    September 4, 2014 - 16:05 | Permalink

    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.

    • 25
      October 16, 2014 - 10:52 | Permalink

      Hi Alex,

      This functionality is available in the standard pivot table object.

      Kind regards,
      Barry

  • 26
    September 4, 2014 - 21:08 | Permalink

    Barry,

    Thanks a lot! This will be usefull to me!

    Regards,

    Marcelo

  • 27
    Jelco Schoolmeester
    October 2, 2014 - 17:27 | Permalink

    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

    • 28
      October 16, 2014 - 11:05 | Permalink

      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

  • 29
    Youri
    October 3, 2014 - 15:18 | Permalink

    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

    • 30
      October 16, 2014 - 10:58 | Permalink

      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

  • 31
    Swapnil
    October 7, 2014 - 13:02 | Permalink

    Hi,

    Is there any option to ‘Hide Excluded’ in the presentation tab that is present in original multibox.

    Thanks

    • 32
      October 16, 2014 - 11:03 | Permalink

      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

  • 33
    October 14, 2014 - 12:07 | Permalink

    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

  • 34
    Simona
    December 2, 2014 - 17:56 | Permalink

    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!

  • 35
    Dena
    January 24, 2015 - 17:15 | Permalink

    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.

  • 36
    John O'Mahoney
    February 23, 2015 - 16:38 | Permalink

    Hi Barry
    Nice solution – very simple but effective
    thanks

  • 37
    tim
    March 23, 2015 - 15:58 | Permalink

    Hi, is there any way to hide unavailable options is in list boxes?

  • 38
    Angad
    March 24, 2015 - 09:48 | Permalink

    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

  • 39
    ueno kazuma
    April 24, 2015 - 05:07 | Permalink

    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

  • 40
    NIck Rigachny
    August 31, 2015 - 10:26 | Permalink

    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.

  • 41
    Ateş Tanju
    October 12, 2015 - 09:31 | Permalink

    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!

  • 42
    Dilip Ranjith
    March 15, 2016 - 17:13 | Permalink

    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

  • 43
    John McCarthy
    June 2, 2016 - 20:52 | Permalink

    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?

  • 44
    July 13, 2016 - 23:46 | Permalink

    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

  • 45
    Tinu
    August 19, 2016 - 07:50 | Permalink

    Hello, Is it possible to limit the drop down to say, 10 values rather than showing everything.

  • 46
    Luigi
    December 11, 2016 - 15:21 | Permalink

    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

  • 47
    Martha Thompson
    January 9, 2018 - 23:25 | Permalink

    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.

  • 48
    Jim
    March 22, 2018 - 17:18 | Permalink

    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

  • 49
    Luigi Tinnirello
    October 21, 2018 - 17:17 | Permalink

    Great!
    What happen to me is tahat I am able to get ONLY the horizontal solution, but I like the vertival one as well.
    How can I force the vertical solution?
    Thanks
    Luigi Tinnirello
    (can you reply to my email?
    [email protected]

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Aliases, to indent or not?

    "Code is read more often than it is written"   - common programming adage As someone who often has to...

    Close