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
