How do I filter the items that are displayed in a QlikView listbox object in a script ?

Dear All,

I have a QlikView dashboard which displays two pivot tables (one for London sales and another for Manchester sales). These pivot tables are placed within a container object so that selecting a pivot table, replaces the one displayed in the container.

I have a list box entitled 'Salesperson' which lists the sales personnel for both London and Manchester. When a pivot table is selected I want the list box to display only those salespersons related to that pivot table (that is, London or Manchester, not both).

How do I script for this ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
Rather than duplicating the pivottable for each Areas sales, I would have a listbox for containing the areas and the one pivot table that changes based on the selection of the Area.  Also by doing this, the listbox for the SalesPeople would automatically update and how only those relevant.  This also give the flexibility of showing one or multiple Areas (London, Manchester etc) on the one pivot table or graph.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobOwner (Aidellio)Commented:
You're now delving in the area of "what really do people want to see".  It drove me nuts designing it one way for someone, only for another to ask for a different way.  I eventually built in the flexibility so they could do their own.
TocogroupAuthor Commented:
Hi. Yes you're right....user requirements !

However, the users (Marketing, not Sales) are adamant they want it as I described it. There are only two sales regions. And the two pivot tables are different in structure even though some of the fields are common to both. Salespeople is one of those common fields.

Any ideas ?
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

RobOwner (Aidellio)Commented:
Unfortunately you can't.  Clicking on the pivot table doesn't raise an event that you can capture nor is inherent in the way Qlikview mashes its data when you make selections

What I would try first is to use a listbox for each SalesPerson region and put it horizontally under each pivot table.  You can achieve this with a calculated dimension like: =If([Region]='London', SalesPerson).  Then just suppress NULL values on the dimension and you'll only ever get a list of SalesPerson for that region.
You can do a similar thing with the pivot table's expression such that you're only capturing sales for the SalesPerson in that region without having to actually select anything: e.g. =SUM(If([Region]='London', SalesValue, 0))

You're on track to eventually needing the aggr() function so it's worth reading up on it now.
TocogroupAuthor Commented:
Yes, I guess I expected to find some sort of VBA-style event procedure in QlikView.

I'm leaning back towards your earlier suggestion and driving the dashboard from a Location listbox with a single pivot table, as the user has informed me they need a 'third' pivot table for Glasgow. I'll go back to QlikView basic functionality instead of reinventing the wheel.

I'll prep up on the AGGR function as suggested.
TocogroupAuthor Commented:
Thanks for your help again
RobOwner (Aidellio)Commented:
No problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.