How to create a dynamic dimensional function that will replace text where ranking is greater than X with "other" text


may be you should try something like this and let me know it gives you what exactly you wanted.

ifelse(ranked_fisical_amount>= 6, sumover(net_fisical_amount, [product]), net_fisical_amount)

That didn’t work. I really need the product category to change to “Other” if the ranked amount > 5.

Like this


Then have the the function treated as a dimension.

hi, could you please provide more details on this case? As I test in house, it works… Any error you are seeing? or The results are not as you expected?

Here is additional information.

Sample Data:

Added Custom Calculated Field To Analysis

  [sum({net_fiscal_amount}) DESC]

Using Calculated Field To Use Rankings


Error Message:

Mismatched aggregation. Custom aggregations can't contain both aggregated and nonaggregated fields, in any combination.


An example with parameters available :

Please try the following and use it in your grouping , it’s using Order of evaluation in Amazon QuickSight - Amazon QuickSight :

  [(Sales) DESC],[],PRE_AGG

Test :

Grouping by license : ifelse({ranked_sales}>5,"Other",License)

Yes, Koushik’s solution is correct. We have to add the “PRE_AGG” into the rank calculation to bring the rank before the visual level calculation. Otherwise, in the ifelse calculation, the condition branch is post aggregation, while the true/false branch are before aggregation. That’s why QuickSight return an error.