Nesting aggregation for ifelse

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.

Can you please help?

Thanks,
Cindy

Hi @Cindy,

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}]))
1 Like

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}]),

${customerVSrev}=‘Revenue’,sum({rev_gar}),NULL)

Sorry, my brackets were in the wrong places.

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

In your calculated field:

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

Hey David,

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:

avg(	
	ifelse(
		${BarChart} = 'No. of Customer', {sfdc_customer_id},
		${BarChart} = 'Revenue', {rev_gar},
		${BarChart} = 'Incremental Revenue', {incremental_revenue_csm}, 
		${BarChart} = 'Incremental Profit', {incremental_profit_csm}, 
		${BarChart} = 'ROI', {roi_csm}, 
		null
	)
)

If this works, we can come back to the avg of distinct_count.

image

Thanks david. But there are errors in the ifelse statement

I also tried put the ifelse inside of the aggregation. but got the same error code. ‘Nesting of aggregated function in ifelse is not allowed’

It’s the way that you’re nesting the aggregations.

This is invalid:

avg(distinct_count({sfdc_customer_id}))

This is valid:

avg(distinct_count({sfdc_customer_id}, [{category}]))

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.