I’m working on automatically grouping brands into an “Other” category based on their rank in Placements, ensuring we display the entire 100% share of placements at each retailer.
Currently, QuickSight allows us to filter the top 5, 10, or 20 brands, but only in alphabetical order, not based on Placements or SKUs. As a result, we are not always showing the top brands by placements, but rather the first brands alphabetically, which isn’t ideal.
Objective:
Ensure that brands with the highest Placements are included in the ranking.
All other brands should be grouped into an “Other” category.
When hovering over the bars in QuickSight visualizations, the number of displayed brands varies.
When hovering over the bars in QuickSight visualization -We Can see 3 brands for some retailers ,1 brand for other retailers even though we have 5 or more than 5 brands for those particular retailers.
% Values are mismatch when calculated manually.
Could you please help in resolving this issue so that the top 5 brands are accurately displayed, and the rest are grouped into “Other”?
Attached is the reference image which shows what exactly we are looking for.
Hi @Nikhilburhade,
While I don’t have a sample dataset similar to this build that I can test this out; what if you created a denserank for your brands based on placement. Then you can use that field in your x-axis and instead of applying a top/bottom filter, limit the number of data points to show (5 if you want the top 5) and that will automatically group the remaining values in to an ‘other’ category.
If this does not work for your case; it would be a lot easier to assist further if you’re able to upload an sample copy of your analysis to the QuickSight Arena view as that will be a lot easier to test out a potential work around.
In the attached visual i am now able to achieve the top 5 brands + Others but now the requirement is - We want the top ranked brands for ALL placements, not within each merchant so that only the top 5 would show in the legend, like in the attached fake example.
Each brand may not be top ranked at each retailer, but they are top ranked overall
Brand_Rank :- rank([{Total_Placements_Per_Brand} DESC], [], POST_AGG_FILTER)
(Here PRE_FILTER ,PRE_AGG is not working it is giving error - For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated. )
Brand_Grouped :- ifelse({Brand_Rank} <= 5, {brand_name}, "Other")
(getting error - Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination)
Attached is the screenshot of what we have currently and what we are looking for.
Hi @Nikhilburhade,
I’m not quite sure I understand your new ask, but you could try and create a calculated field to rank your brands, then an ifelse that recognizes the ranks of 1-5 in that ranking, then groups all the other options in to an ‘other’ category. The issue you might encounter though is if each placement has different top 5 brands, it may have issues displaying.
If you’re looking to accomplish something different or need additional assistance, please create an Arena view with sample data so that I can test out.
