List Box to Select Values From Two Fields at Once

I posted this answer on QlikCommunity yesterday and several people remarked on it so I thought I’d repeat it here.

I recently had a QlikView document that showed lagged sales forecast data. There were two date fields used in the data: a Create_Date indicating when the sales forecast was created, and, a Fcst_Date indicating which week was being forecasted. For example, a forecast of sales for the week of June 21st might be created two weeks earlier on June 7th. Then the Create_Date would be 07JUN2012 and the Fcst_Date would be 21JUN2012.

In the document, in addition to other list boxes and a multibox, I added a list box that allows a user to view and select values from both date fields at the same time.
I created a new List Box and in the Properties General tab, I didn’t select a field, instead I chose <expression> and typed this into the expression:
='Create date=' & Create_Date & ' and ' & 'forecasted week=' & Fcst_Date
I made the title of the list box “Select Create and Forecasted Dates”.
Now, I had a list box that showed the pairs of dates that occur in the data. The values in the list box can be green when they are selected or white when they are possible values and gray when they are excluded values just like any other list box. When I click on a value in the list box it selects the corresponding values from each of the two date fields at the same time.

One additional refinement was to make the values sort the way I want them. I opened the Properties of the list box and chose the Sort tab and checked the Expression box and then in the expression box I typed just the field name, Create_Date

This idea would work with any pair (or trio) of fields where being able to view the field values together and select the values together makes sense.


  *  *  *