topic Re: How do I limit the items displayed in a list box ? in QlikView

How do I limit the items displayed in a list box ?

Wed, 03 Sep 2014 13:46:21 GMT

Dear All,

On my dashboard I have a container object comprising two pivot tables: one showing sales information for Manchester, and the other showing sales info for London. I also have a list box called SalesPerson.

When I select the Manchester pivot table I only want to display Manchester salespersons in the list box. Similarly, if I click on the London pivot table object, I want to see ONLY London sales persons in the listbox (not Manchester's).

To help you, each Salesperson has a field called Sales_Location associated with it.

How do I code for this ? And where ?

Thanks

PS This is not a Hide Excluded solution.

Re: How do I limit the items displayed in a list box ?

MK_QSL — Wed, 03 Sep 2014 13:54:23 GMT

Use a SalesPerson List box with expression as below

Aggr(SalesPerson,SalesPerson)

Re: How do I limit the items displayed in a list box ?

Anonymous — Wed, 03 Sep 2014 13:55:45 GMT

Maybe load separate lists for London and Manchester salespersons and put a trigger on the pivot tables to show/hide relevant list boxes

Re: How do I limit the items displayed in a list box ?

Wed, 03 Sep 2014 14:34:28 GMT

Hi,

This doesn't work. It just creates two columns of identical items of salespersons in the list box.

Re: How do I limit the items displayed in a list box ?

Wed, 03 Sep 2014 15:00:12 GMT

Yes, that seems a more promising solution. Would you load separate QV tables in the script ? And create two distinct list box objects ?

How could you 'trigger' the show/hide of the listboxes ?

Re: How do I limit the items displayed in a list box ?

Anonymous — Wed, 03 Sep 2014 15:32:16 GMT

Thinking about this again what you would need to do is have a text object(s) used as a button which hides the pivot table AND the list box.

Firstly set up a variable (go to Settings > Variable Overview > Add the add a new variable called vLocation (for example). You don't need to define it.

You could have two text objects, one with the text 'Manchester' and the other with the text 'London'. In the Manchester text object properties go to the Actions tab then click Add. Choose Action Type 'External' and Action 'Set Variable' then click OK. In the Variable box put vLocation then in the Value box put Manchester. Do the same thing for the London text object but with London as the variable value.

In the pivot table and list box properties in the Layout tab in the Show section click on conditional and type vLocation='Manchester' (or 'London') for the relevant chart/list box. When you click on the text object its sets the variable to whichever location and shows the right sheet and hides the other. You can also change the colour of the text object based on the variable if you want to show which button is clicked or not.

Re: How do I limit the items displayed in a list box ?

IAMDV — Wed, 03 Sep 2014 15:58:09 GMT

Hi Maureen,

See the attachment and see if this approach helps?

Cheers,

www.QlikShare.com

Re: How do I limit the items displayed in a list box ?

Fri, 05 Sep 2014 10:29:27 GMT

Oh yes ! That works. Thank you for a good idea and explaining it very clearly.

Regards

Re: How do I limit the items displayed in a list box ?

Anonymous — Fri, 05 Sep 2014 10:36:53 GMT

You're welcome!

Re: How do I limit the items displayed in a list box ?

Fri, 05 Sep 2014 12:15:18 GMT

One final thing. How do you change the background colour of the text object depending on the variable ?

Re: How do I limit the items displayed in a list box ?

Anonymous — Fri, 05 Sep 2014 13:11:55 GMT

In the Text object properties go to the General tab and under Background click on the color box. Under Base Color click the calculated tick box and define the expression (for the Manchester button)

if(vLocation='Manchester',rgb(a,b,c),rgb(x,y,z))

Obviously the a,b,c and x,y,z are the red/green/blue numbers

Regards

Adrian

Re: How do I limit the items displayed in a list box ?

rustyfishbones — Fri, 05 Sep 2014 13:16:35 GMT

Hi Deepak,

I like your approach.

I would normally use AGGR(SalesPerson,SalesPerson)

Re: Re: How do I limit the items displayed in a list box ?

IAMDV — Fri, 05 Sep 2014 13:30:17 GMT

Cheers mate. Dollar sign expansion is very powerful way to generate dynamic fields and their values. Probably, it's a subject for my next blog post.

Re: Re: How do I limit the items displayed in a list box ?

rustyfishbones — Fri, 05 Sep 2014 13:36:08 GMT

I look forward to it Deepak

btw. can you be voted for the Qlik Luminary class of 2015 or can you only be nominated once

i.e. once a Luminary always a Luminary, or do you have to prove yourself each year?

Re: How do I limit the items displayed in a list box ?

Fri, 05 Sep 2014 13:37:35 GMT

That's excellent. many thanks for your help.