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

Question & Answer
, ,
1

Example,

image
image1005×630 50.7 KB

2

Hi
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)

3

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

Like this

ifelse({ranked_fiscal_amount}>5,"Other",[product])

Then have the the function treated as a dimension.

4

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?

5

Here is additional information.

Sample Data:

Added Custom Calculated Field To Analysis

rank(
  [sum({net_fiscal_amount}) DESC]
)

Using Calculated Field To Use Rankings
image

ifelse({ranked_fiscal_amount}>5,"Other",{product})

Error Message:

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

Hi,

An example with parameters available : https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Dynamic-Others-Group

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

rank(
  [(Sales) DESC],[],PRE_AGG
)

Test :

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

Screenshot 2022-06-21 at 16.13.41
Screenshot 2022-06-21 at 16.13.41920×534 60.7 KB

7

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.