Hi,
I have a calculated field: If the ${customerVSrev} paramater = ‘No. of Customer_’, then get the count of customer_. if ${customerVSrev} paramater=‘Revenue’ then get the sum of revenue.
ifelse(${customerVSrev}=‘No. of Customer’,distinct_count({sfdc_customer_id}),
${customerVSrev}=‘Revenue’,sum({rev_gar}),NULL)

users are asking if we can provide the mean for those. Meaning update the logic to become:
If the ${customerVSrev} paramater = ‘No. of Customer_’, then get the MEAN of the count of customer_. if ${customerVSrev} paramater=‘Revenue’ then get the MEAN of revenue. I tried to add the avg(), but then got error code that: nesting aggregation for ifelse statement.

If you want to calculate the mean, you need to use LAC-A. You need to specify by what field to group your inner aggregation (the distinct count or sum) before you wrap it in an avg function.

Something like this:

avg(distinct_count({sfdc_customer_id}, [{group by field}]))

Thanks for the reply David
I’ve tried to do that, but got the same error like ‘Nesting of aggregated functions is not allowed in ifelse/case when’. Below is the calculated field I am using

ifelse(${customerVSrev}=‘No. of Customer’,avg(distinct_count({sfdc_customer_id}), [{category Combination}]),

I am having the same error code with your new suggested calculations.
‘nesting aggregation for ifelse statement is not allowed’. I think the problem is more at how can we combine the avg of count with ifelse statement

Normally I would put the “ifelse” inside the aggregation but the problem is that not all your aggregations are the same. Let’s forget about the avg of distinct_count for now and try this:

Try the 2 above calculations separately without any ifelse function and you’ll see what I mean. The second one is called LAC-A. You’ll have to figure out what to group your distinct_count by and put that field inside the square brackets.