Prior to the introduction of Listbox Expressions in QV11, the only additional data you could put in a listbox was Frequency. The Show Frequency option has some shortcomings:
The meaning of frequency is dependent on the data model. For a customer name in an orders table, frequency may correctly reflect the order count. If the customer name is moved to a dimension table, the frequency is “1” .
Frequency values show only for possible rows — green and white. Excluded gray rows show nothing in the Frequency column.- Show Frequency is not available for Key fields.
Listbox Expressions solve these problems and more. Here are are a few tips on using Listbox Expressions.
On the Expressions tab of a listbox properties, you can add one or more expression columns, as you would in a chart. The “Dimension” will be the listbox field.
While the dialog may be similar to a chart, there are a few features that are not available, notably:
- Number format — you must do any desired formatting in the expression.
- Column labels — the workaround is to include the “label” text in the Caption.
- Totals.
If you want your Listbox sorted by the expression value, repeat the expression in the Sort pane “Expression” property. There is no need to include the “Num()” formatting in the Sort Expression, but no harm in leaving it in either.
Now we have a nicely sorted listbox that provides context about “customer size”.
When selections are made, excluded data shows zero, probably not what we want. Fix that up by adding a Set to the expression.
sum({1}LineSalesAmount)
What if we want to the Sales expression to reflect other selections, like Product, but still want to see all Customers? Simple, add a set modifier to ignore Customer.
sum({<Customer=>}LineSalesAmount)
Like a chart, we can add additional expressions such as order count, days since last order, or account rep name. We also have the full range of expression representations; Image, Gauge, Mini Chart etc. We can even put pictures in the listbox.
There is no visible vertical line separating columns, but the columns may be resized by dragging at the invisible boundaries.
If you get frustrated trying to make a listbox look like a chart, take a step back. Listbox expressions are meant to guide the user in making selections, not present a finished analysis.
-Rob
The expressions get even more useful when the listbox is for a cycle group instead of a single field. Click through each option or right-click to see the menu of available choices.
I especially like a cycle group made up of field values satisfying a condition (done with expressions like calculated dimensions). So instead of the Customer field, it could be a cyle group with choices like “new customers”, “export customers”, “priority customers”, “high-profit customers”, etc.
Thanks for the post!
Great comment Tim. I find using a cycle group in a listbox is a great way to save space.
A cycle group made up of calculated dimensions is intriguing.
I also find that Drill groups can also be very useful in a listbox.
Thank you Rob.
I’ve used the ListBox Expressions on a large application (i.e Large Datasets with Straight Tables) as “Data cubes”. Where I’ve listed two ListBoxes – One with the Field Name and Second as Field Value with the expression counts. This way, users can select any field from the model and look at the possible field values along with the counts. This was very powerful and flexible as it helped users to Filter the data first and then select the large straight table instead of other way around.
Cheers,
DV
How could we embed a PowerPoint slide as a background in the QlikView application ? Please let know. Thanks a lot.
I don’t know of any way to directly embed a PowerPoint slide as a background. But you could convert a slide to a jpg and use the jpg as a background.
Hi Rob,
I want to create list box of names of fields. (like system field) In the expression I want a distinct count of the values in that field.
I’m trying to present a quick global view of the data
Many thanks
Neil
Hi Neil,
Use [$Field] as your listbox field or chart dimension.
For expression use:
=FieldValueCount([$Field])
Hi Rob
I have a list box with a few expressions. I need to have it first sorted by category (of which there are 100). Then I need it sorted by value. In the sort expression I’m able to write an expression to sort it by either category or value.
Do you have any advice?
A sort expression something like:
=Category & ‘-‘ & Value