Im trying to create a field that return name_detail if product rank less than the ‘Top’ parameter. Else it return ‘Others’.
But even when I followed the syntax, I’m not able to generate the calculated field. (I’ve tried creating it several times. same error occured)
Hi @danial,
when I tried to reproduce your error, I received the following error message
Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.
To fix this, you need to use an aggregation function (like lastValue) when you want to leverage name_detail in the else branch of the ifelse function.
Based on your provided information, I created a small example that uses the following expression in the top_quantity field
ifelse(
rank([sum(Sales) DESC], [Industry,Product])> ${top},
'Others',
lastValue(Product, [Industry ASC],[Industry,Product,Customer]))
that then leads to the following output in a table visual:
Note that I had also removed the sumOver from the calculation, as otherwise I would have received the same aggregated value for all of my customers within the same Industry+Product combination - which is usually not what you want when trying to rank to find the Top N entries. If your use case is different, please don’t hesitate to share it with us.
Did this answer your question? If so, please help the community out by marking this answer as "Solution!
Thank a lot @Thomas, I will try this.
Hi, @Thomas. I’ve tried using your example: (my field below)
ifelse(
rank([sum({quantity_detail}) DESC], [{brp_cust_name},{name_detail}])> ${Top},
‘Others’,
lastValue({name_detail}, [{brp_cust_name} ASC],[{brp_cust_name},{name_detail}]))
but it doesn’t seem to work for my visualization. I’m trying to create a new field which returns the ‘name_detail’ (if less/equals than top), else ‘Others’. Then use the new field in donut chart below (replace name_detail), so that when users adjust the ‘Top’ Filter, the chart will also change to display the name of the Top N products only (otherwise, product name will be ‘Others’)
(On chart above I just put the ‘number of charts display’ to 5)
Can you help me with this?
As custom aggregation fields are currently not supported as a dimension, the approach mentioned above does not work with that chart type. To still get a dynamic filter for top N elements in that visual, you could implement a simple rank field and then use that to filter out all items that don’t fall within the top N elements. You would then only miss a direct indication of how much went into the “Other” category.
Depending on your datasource, you might also be able to leverage a dataset parameter in combination with a custom SQL query (see example) and add the “top X elements” logic into that query instead. This would allow you to still get all rows that sit outside the top elements grouped into an “Others” bucket.
Did this answer your question? If so, please help the community out by marking this answer as "Solution!
