Not able to do the calculation to compare 2 aggregated values and a dimention

Q&A
, ,
1

For a specific dimension I need to compare 2 different levels of aggregations and conditionally format the columns based on the calculations. My existing calculation is

image
image889×370 10.7 KB

And based on the value of Flag I need to color code a column.

But here in this calculation aggregated level is another calculation , That will change based on the Benchmark parameter selection.

image
image823×241 6.44 KB

Now my requirement is I need to do the comparison for avg of those metrics, and while doing that I am getting Aggregation Mismatch error

Flag=
ifelse(
({comparative_metric_category}=‘Retain’ AND avg({store_aggregated_count})< avg({Aggregation level})) OR
({comparative_metric_category}=‘Train’ AND avg({store_aggregated_count})> avg({Aggregation level})) OR
({comparative_metric_category}=‘Engage/EVP’ AND avg({store_aggregated_count})> avg({Aggregation level})) OR
({comparative_metric_category}=‘Recruit’ AND avg({store_aggregated_count})> avg({Aggregation level})) OR
({comparative_metric_category}=‘Staffing’ AND avg({store_aggregated_count})> avg({Aggregation level})) OR
({comparative_metric_category}=‘Wages’ AND avg({store_aggregated_count})> avg({Aggregation level})),1,
avg({store_aggregated_count})= avg({Aggregation level}),2,
0)

But I am getting the below error

image
image1828×451 43 KB

I tried of creating Individual Avg value calculations for all levels and used that, still i am getting that error.

Can anyone help to resolve this calculation issue?

2

Hi @Ganga,

You’re getting a mismatched aggregation error because your ifelse function is combining a condition based on a single row value for comparative_metric_category and an aggregated value. That’s not allowed.

Try to change your avg to avgOver with PRE_AGG. You’ll have to figure out what partition to use for your avgOver function though. I would have to see what type of visual you’re trying to create to know what your correct partition should be.

You can read about the LAC-W functions here:

2 Likes
3

Hi @Ganga,
It’s been awhile since we last heard from you. Wanted to check in and see if David’s response was helpful regarding your question or if you have any additional questions regarding it?

If we do not hear back from you within the next 3 business days, I’ll go ahead and mark as the solution; past that though if you have any further questions, feel free to create a new topic and link this discussion for relevant information.

Thank you!

1 Like