Hello all,
I have a rather complicated set of rules to create a bucket which classifies Vendors as 'High','Medium' or 'Low'. I have the data as follows:
| Vendor | Sales | Type | Type1 | No.Of Alternates | Score1 | Score2 | Score3 |
| T | 531.88 | C | Inter | 357 | 1 | 3 | 1 |
| L | 405 | C | Trade | 252 | 1 | 3 | 1 |
| F | 110.5 | C | Inter | 47 | 1 | 3 | 1 |
| V | 5101.6 | NC | Inter | 1778 | 1 | 3 | 1 |
| U | 45.35 | NC | Inter | 84 | 1 | 3 | 1 |
| U | 30.43 | NC | Inter | 84 | 1 | 3 | 1 |
| L | 196 | C | Inter | 425 | 1 | 3 | 1 |
| F | 91.77 | C | Inter | 107 | 1 | 3 | 1 |
| D | 103.5 | NC | Inter | 83 | 1 | 3 | 1 |
| S | 594 | NC | Inter | 53 | 1 | 3 | 1 |
| I | 2470.29 | C | Inter | 6 | 1 | 2 | 1 |
| N | 45 | NC | Inter | 121 | 1 | 3 | 1 |
| Q | 120 | NC | Inter | 379 | 1 | 3 | 1 |
| X | 439.84 | NC | Inter | 4 | 1 | 1 | 1 |
| H | 78.72 | C | Inter | 495 | 1 | 3 | 1 |
| H | 7924.44 | NC | Inter | 38 | 1 | 3 | 1 |
| M | 430.5 | C | Inter | 15 | 1 | 3 | 1 |
| L | 2919.24 | NC | Inter | 217 | 1 | 3 | 1 |
| L | 2229.74 | NC | Inter | 217 | 1 | 3 | 1 |
| C | 231777 | C | Inter | 99 | 1 | 3 | 1 |
| A | 249.24 | NC | Inter | 293 | 1 | 3 | 1 |
| F | 96 | C | Inter | 22 | 1 | 3 | 1 |
| E | 1831.8 | NC | Trade | 10 | 1 | 2 | 1 |
| G | 63.42 | NC | Trade | 9 | 1 | 2 | 1 |
| T | 86.11 | C | Inter | 357 | 1 | 3 | 1 |
| T | 142.04999 | C | Inter | 357 | 1 | 3 | 1 |
| H | 197.22 | C | Inter | 318 | 1 | 3 | 1 |
| H | 18.38 | C | Inter | 318 | 1 | 3 | 1 |
| H | 33 | C | Inter | 318 | 1 | 3 | 1 |
| D | 20.96 | C | Inter | 436 | 1 | 3 | 1 |
| D | 18.9 | C | Inter | 436 | 1 | 3 | 1 |
| B | 13.85 | NC | Inter | 253 | 1 | 3 | 1 |
| B | 110.3 | NC | Inter | 253 | 1 | 3 | 1 |
| B | 1.52 | NC | Inter | 2 | 1 | 1 | 1 |
| B | 163.96 | NC | Inter | 253 | 1 | 3 | 1 |
| B | 2 | NC | Inter | 1 | 1 | 1 | 1 |
| B | 0.98 | NC | Inter | 2 | 1 | 1 | 1 |
| B | 65.78 | NC | Trade | 253 | 1 | 3 | 1 |
| W | 82746 | NC | Trade | 383 | 1 | 3 | 1 |
| J | 48.99 | NC | Trade | 67 | 1 | 3 | 1 |
| P | 78.61 | C | Inter | 416 | 1 | 3 | 1 |
| P | 82.83 | C | Inter | 416 | 1 | 3 | 1 |
| P | 95.42 | C | Inter | 416 | 1 | 3 | 1 |
| P | 36 | C | Inter | 416 | 1 | 3 | 1 |
| O | 496.80002 | NC | Inter | 83 | 1 | 3 | 1 |
| O | 900 | NC | Inter | 83 | 1 | 3 | 1 |
| K | 91037.24 | C | Inter | 1205 | 1 | 3 | 1 |
| R | 160 | NC | Inter | 1778 | 1 | 3 | 1 |
| I | 1750 | C | Inter | 1778 | 1 | 3 | 1 |
| I | 1950 | C | Inter | 1778 | 1 | 3 | 1 |
Now what I'd like to do is as follows:
1.Perform a Pareto analysis on the above data and classify the Vendors into 3 classes: Top 80%,80-95% and Bottom 5%.
2.Based on the Vendor's Pareto class, I'd like to add another Score criteria, let's say Score 4. Score 4 should be allotted as follows:
(a).If the Vendor falls in Top 80%, allot Score4 as 1.
(b).If the Vendor falls in 80-95%, allot Score4 as 2.
(c).If the Vendor falls in Bottom 5%, allot Score4 as 3.
3.After completing the Score4 column, now sum up all the Score Columns, Score1 through Score4. Let's call it Total Score.
4.If Total Score <=7, Bucket is 'Low'. If 7<Total Score<=10, Bucket is 'Medium'. If Total Score>10, Bucket is 'High'.
Now, there are some exceptions to the above rules as well:
(a). If Type='C', the Bucket should automatically be 'Low'.
(b).If Type1='Trade', the Bucket should automatically be 'Low'.
(c).If No.Of Alternates < 3, the Bucket should automatically be 'Low'.
In short if any one or more of the above criteria are met for the Vendors, the Bucket should be 'Low'.
Added to my complications, all this should be implemented in the front end, as a Calculated Dimension. This is way out of my little knowledge of Qlik Sense and I badly need help to implement this. I'd be grateful to anyone who'd hep me out in this! TIA!
stalwar1 Help me out kind sir!
