QlikView - simulate excel list boxes

Wed, 22 Jan 2014 10:19:58 GMT

Hello all,

I am working on a dashboard and the core of the problem is to duplicate excel functionality.

In excel spreadsheet I have customers and each customer has 5 same criterias. Those customers are next each other in a row. And criterias are shown on left side. The intersection of each customer vs. criterium is a listbox - where user can select criteria values. Values are represented by value name and besides that, there is a hidden numeric value which represents value name for calculation. Criteria values are same for each customer. On selecting criterium values from listbox also numeric value is selected in behind and calculation of Score is made. Score calculation can be described as a sum of numeric values for selected options from listboxes. This sum is for each customer. Following table explains this red options are selected in listbox:

Excel spreadsheet:

Criterium	Customer1	Customer2
Share	Listbox for Share: Low (1), Medium (2), High (3)	Listbox for Share: Low (1), Medium (2), High (3)
Growth	Listbox for Growth:Slow (1), Medium (2), Fast (3)	Listbox for Growth:Slow (1), Medium (2), Fast (3)
Demand	Listbox for Demand:Lower (1), Higher (2)	Listbox for Demand:Lower (1), Higher (2)
Calculated Score	Sum of selected (numeric) values = 5	Sum of selected (numeric) values = 4

And now I need to recreate this functionality in QV - I tried to do this by creating data model shown below:

Share Criterium Table:

Criterium_ID	Criterium	Criterium Data	Data Level
ID_SH_1	Share	Low	1
ID_SH_2	Share	Medium	2
ID_SH_3	Share	High	3

Share Citerium vs. Customers (connect) table:

Customer ID	Criterium_ID
ID_CUS_1	ID_SH_1
ID_CUS_1	ID_SH_2
ID_CUS_1	ID_SH_3
ID_CUS_2	ID_SH_1
ID_CUS_2	ID_SH_2
ID_CUS_2	ID_SH_3

First table stores one criterium with possible options and numeric values and another one connects these data with customers - it stores Customer ID and ID of all possible options for selection from first table. The problem is after selection of data in listbox built on this model is that selected values are applied on all customers therefore all have the same score. I need to make this seperate for each customer.

Maybe there is completely another way like storing Criterium name + value into variables and then somehow use listboxes, but in the end I need this functionality to be connected on customers.

Thank you all for reading and suggestion,

Alex

topic QlikView - simulate excel list boxes in QlikView

QlikView - simulate excel list boxes