Peter Davison
Active Member
- Joined
- Jun 4, 2020
- Messages
- 437
- Office Version
- 365
- Platform
- Windows
Hi,
I've been doing a bit of store clustering on a spreadsheet (using a sort of K Means type formula and whilst I can cluster a single attribute I'm not sure how I can establish what the store cluster would be for a group of attributes.
For Example
For each attribute individually - Fruit, FTG Salads etc I have established the Min and Max % contribution and what the whole stores average % is. (Above)
and then clustered using 5 clusters (could be any number) and just 10 iterations which then places each store in one of the 5 clusters During the 10 iterations some stores move cluster to be closer to each new centre of the cluster.
This is an example of a few stores below
Each % by store will add up to 100% for all attributes.
What I can't work out is for each attribute Fruit, FTG Salads etc the store may be in a different cluster, so what would I do if I wanted to find what cluster a store should be in taking into consideration all 6 attributes - Fruit, FTG Salads, Salad Additions, Salads, Stir Fry and Veg?
I always set the cluster starting point lowest% to highest%
At the moment I have a sheet for each attribute.
If anyone has any ideas how I could do this using a spreadsheet I would be really pleased.
I've been doing a bit of store clustering on a spreadsheet (using a sort of K Means type formula and whilst I can cluster a single attribute I'm not sure how I can establish what the store cluster would be for a group of attributes.
For Example
Min | 2.46% | 3.45% | 1.29% | 22.42% | 8.40% | 20.25% |
Max | 23.67% | 11.48% | 4.65% | 47.05% | 18.19% | 40.94% |
Grand Total | 12.68% | 6.09% | 3.16% | 34.74% | 14.33% | 29.00% |
FRUIT | FTG SALADS | SALAD ADDITIONS | SALADS | STIR FRY | VEG |
For each attribute individually - Fruit, FTG Salads etc I have established the Min and Max % contribution and what the whole stores average % is. (Above)
and then clustered using 5 clusters (could be any number) and just 10 iterations which then places each store in one of the 5 clusters During the 10 iterations some stores move cluster to be closer to each new centre of the cluster.
This is an example of a few stores below
Store | FRUIT | FTG SALADS | SALAD ADDITIONS | SALADS | STIR FRY | VEG |
101 | 13.04% | 5.83% | 3.76% | 32.68% | 15.16% | 29.54% |
102 | 16.17% | 7.21% | 3.24% | 29.47% | 13.43% | 30.49% |
104 | 9.97% | 5.44% | 2.04% | 41.25% | 14.96% | 26.35% |
105 | 10.92% | 4.11% | 2.19% | 42.08% | 14.50% | 26.19% |
108 | 12.66% | 6.64% | 2.97% | 36.19% | 14.62% | 26.92% |
109 | 9.42% | 7.19% | 2.44% | 27.28% | 15.54% | 38.13% |
Each % by store will add up to 100% for all attributes.
What I can't work out is for each attribute Fruit, FTG Salads etc the store may be in a different cluster, so what would I do if I wanted to find what cluster a store should be in taking into consideration all 6 attributes - Fruit, FTG Salads, Salad Additions, Salads, Stir Fry and Veg?
I always set the cluster starting point lowest% to highest%
At the moment I have a sheet for each attribute.
If anyone has any ideas how I could do this using a spreadsheet I would be really pleased.