• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2634
  • Last Modified:

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 ?

Regards
Toco
0
Tocogroup
Asked:
Tocogroup
  • 4
  • 3
1 Solution
 
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.
0
 
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.
0
 
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 ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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.
0
 
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.
0
 
TocogroupAuthor Commented:
Thanks for your help again
0
 
RobOwner (Aidellio)Commented:
No problem.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now