Hi I have tried and just cannot figure this out. Very simple to do in excel but not in QLIK
I need to count at a Region level the number of stores that are in the top 10 for performance of sales against target (goal).
Output of the results are this table which shows how many stores are in the Top 10 in each Region.
E.G. Area_3 has 5 stores 2 have hit their target and 1 is in the top 10.
| Region |
Store Count |
Stores Hit |
Top 10 |
| Area_1 |
4 |
3 |
2 |
| Area_2 |
4 |
2 |
2 |
| Area_3 |
5 |
2 |
1 |
| Area_4 |
6 |
1 |
0 |
| Area_5 |
5 |
1 |
0 |
| Area_6 |
4 |
2 |
1 |
| Area_7 |
3 |
0 |
0 |
| Area_8 |
6 |
4 |
4 |
| Area_9 |
3 |
0 |
0 |
| Area_10 |
6 |
2 |
0 |
Here is the data for the above results which the output is displayed in a table at store level.
Sales_Performance:
LOAD * Inline [
Region, Store, Sales, Target
Area_1, Store_1, 106, 98
Area_1, Store_2, 21, 94
Area_1, Store_3, 130, 96
Area_1, Store_4, 96, 83
Area_2, Store_5, 100, 111
Area_2, Store_6, 120, 125
Area_2, Store_7, 137, 112
Area_2, Store_8, 127, 107
Area_3, Store_9, 67, 114
Area_3, Store_10, 70, 110
Area_3, Store_11, 56, 94
Area_3, Store_12, 104, 99
Area_3, Store_13, 141, 94
Area_4, Store_14, 26, 101
Area_4, Store_15, 126, 122
Area_4, Store_16, 40, 112
Area_4, Store_17, 31, 93
Area_4, Store_18, 75, 118
Area_4, Store_19, 46, 80
Area_5, Store_20, 102, 121
Area_5, Store_21, 118, 125
Area_5, Store_22, 103, 92
Area_5, Store_23, 91, 115
Area_5, Store_24, 63, 125
Area_6, Store_25, 112, 107
Area_6, Store_26, 22, 93
Area_6, Store_27, 75, 85
Area_6, Store_28, 145, 82
Area_7, Store_29, 43, 113
Area_7, Store_30, 118, 125
Area_7, Store_31, 52, 82
Area_8, Store_32, 91, 111
Area_8, Store_33, 42, 82
Area_8, Store_34, 134, 106
Area_8, Store_35, 133, 99
Area_8, Store_36, 144, 109
Area_8, Store_37, 141, 94
Area_9, Store_38, 107, 113
Area_9, Store_39, 96, 115
Area_9, Store_40, 90, 104
Area_10, Store_41, 82, 93
Area_10, Store_42, 95, 83
Area_10, Store_43, 49, 124
Area_10, Store_44, 90, 120
Area_10, Store_45, 71, 92
Area_10, Store_46, 113, 98
];
Example of Store level Table from the data above:
| Region |
Store |
Sales |
Target |
Tgt % |
Hit |
Rank |
| Area_1 |
Store_1 |
106 |
98 |
108.2% |
Y |
14 |
| Area_1 |
Store_2 |
21 |
94 |
22.3% |
|
46 |
| Area_1 |
Store_3 |
130 |
96 |
135.4% |
Y |
4 |
| Area_1 |
Store_4 |
96 |
83 |
115.7% |
Y |
10 |
| Area_2 |
Store_5 |
100 |
111 |
90.1% |
|
22 |
| Area_2 |
Store_6 |
120 |
125 |
96.0% |
|
18 |
| Area_2 |
Store_7 |
137 |
112 |
122.3% |
Y |
8 |
| Area_2 |
Store_8 |
127 |
107 |
118.7% |
Y |
9 |
| Area_3 |
Store_9 |
67 |
114 |
58.8% |
|
36 |
| Area_3 |
Store_10 |
70 |
110 |
63.6% |
|
32 |
TIA
