Urgent_Mismatch aggregation error

Question & Answer
,
1

Hello,

Objective

I am trying to dynamically compute and visualize Top N Brands based on either units or dollars, per month (Date). The setup involves:

  • User selects metric: Units or Dollars
  • User selects Top N: via parameter ${TopN}
  • Brands outside the Top N should be grouped under Others

Current Setup :

A] Get the sum of unit by brand and date partitioning

sum of unit : (have created this at dataset level).
sumOver({units}, [{Brand}, {Date}], PRE_AGG)

B] Calculate minimum of above field because if we do min at visual level then we are getting upto the correct numbers :

min of sum of unit : (have created this at dataset level)
min({sum of unit})

image
image1217Ă—593 61.9 KB

C] Then we want to give rank to min of sum of unit to get up to the correct ranking :

rank to min of sum of unit : (have created this at dataset level)
rank([{Min of sum of unit} DESC],[{Date}],POST_AGG_FILTER)

D] dynamic total to toggle between unit and dollar :

dynamic total (have created this at analysis level)
ifelse( ${UnitDollar} = 'Units', units, ${UnitDollar} = 'Dollars', dollars, 0 )

E] Allow users to select the top N brands, and bucket all others as "Others" using a parameter ${TopN}.

Top N Unit brands (trying to create at analysis level but getting mismatch aggregation error)
ifelse( {rank to min of sum of unit} <= ${TopN}, Brand, 'others' )

And at this stage we are getting mismatch aggregation error
Here top N is the parameter which gives user leverage to select the TopN brands and group rest into others

Same process needs to be repeat for dollars

F] Once Top N Unit/dollar brands (point E) will work then we will have below logic to toggle between units and dollars :

dynamic ranking :

ifelse( ${UnitDollar} = 'Units', {TopN unit brands}, ${UnitDollar} = 'Dollars', {TopN dollar brands}, 'Others' )

What we are looking for :

We want to implement this logic into the vertical stacked bar chart where user can select TopN brand and rest will be grouped into “OTHER”

X axis : Date
Y axis : dynamic total from point F as shown above
Group/Color : newly created calculated field from point F as shown above (dynamic ranking)
Parameter 1 : where user can toggle between units or dollars
Parameter 2: to select topN brand by slider

image
image1672Ă—533 101 KB

And this visual would show data dynamically even with filters like parentproducts , retailer etc.
Note :

  1. We do not want to use the Top and Bottom filter on the Brand field to pass the TopN parameter , because this approach only displays the Top N brands and excludes the rest. Our requirement is to group the remaining brands into an “Others” category rather than filtering them out.
  2. We do not want to use the “Number of bar segments displayed” option available under visual properties because it does not allow users to dynamically select the Top N brands. Instead, our requirement is to provide a parameter-driven and calculated field level approach where users can interactively choose the desired Top N value and have the remaining brands aggregated into an “Others” category.

image
image235Ă—456 12.5 KB

I have updated same setup on arena with mock data.
TopN Brand Analysis

Thank You.

2

Hello @Nikhilburhade

I think you are running into the same issue that’s described in the topic below:

Can you try the steps in the solution in that topic and let me know if it works?

2 Likes
3

Hello @duncan

Thank you so much for your response.
I tried implementing a similar approach in my dashboard, and it is working well — producing the correct numbers.
Below is the approach I followed:

image
image832Ă—657 58.2 KB

Thank You.