Use IfElse to Give Percentile

I have created three columns to show the 25, 50, 75th percentiles in my data. I am now trying to compare one field to these values in an ifelse statement but am getting errors (even when just doing one part, getting the below 25th percentile)

ifelse({Sales}<{Percent25},‘Below 25%’,NULL)

Percent25 is itself a calculated field aggregated at a country and reporting week level.

I get the error that the calculations can’t contain both aggregate and non-aggregated field. How can i fix this?

1 Like

What do you want to compare the sales to Percent25 to essentially? Do you want to compare the sum of sales, the average, or just that rows value?

I’m assuming it’s just the rows value?

In that case you can do a firstValue.

ifelse(firstValue({sales},[{sales} ASC],[{sales}])<{Percent25},‘Below 25%’,NULL)

Yes, I just want to compare each row of a table to the percents. I can get the first value part of your calculation to work, but once i place within the ifelse i get “The calculation operated on Level Aware Calculations agg expressions is not valid”.

ifelse(firstValue({sales},[{sales} ASC],[{sales}])<{Percent25},‘Below 25%’,NULL)

@cactus

Please take a look at this documentation.

There are limitations on how you nest / use calculations.

Hopefully you can use a work around.

While I usually default to the LAC-A expressions because they roll up more nicely, sometimes I revert to the LAC-W functions in cases where I get this error. Try doing something similar with percentileOver formulas (note that these result in a non-aggregated value, so when doing comparisons make sure your other field is non-aggregated, or need to aggregate both to ensure that all fields are either aggregated or non-aggregated in the same calc.

1 Like