Hi all! First time Alteryx user and would appreciate any help. A retail company I am working with has data from the following 3 sources:
1.) All stores and shelve ID within each store
2.) All shelve IDs and all item IDs attached to each shelve
3.) All item IDs and their financial performance in the last year
I am attempting to combine these three data sets to achieve a list of Indvidual Item IDs within tagged subcategories, with a Sales per store value attached to each (calculated by using the sales number for each item ID, and dividing that by the distinct count of stores each item is tagged to).
I then want to rank each of these individual item items in a given subcategory by where they rank for sales per store for all items in the subcategory (similar to percentrank in Excel). I was attempting to use Quintiles, so I could see where each item for Y subcategory sits in comparison to others.
The problem that I was running into was that once the data was run, I ended up with unequal quintiles - there should be an equal distinct item count in every 20% of the subcategory item count. If there are 100 items total, there should be 20 in each quintile, with the top 20 being those with the highest sales/store. Instead, I was getting uneven quintiles.
Apologies for the long message, just wondering if anyone could help. Thanks in advance.
