Nesting aggregation for ifelse

Question & Answer
, , , ,
1

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

2

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
3

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)

4

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

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

image
image986×245 6.78 KB

6

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.

7

image

Thanks david. But there are errors in the ifelse statement

8

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’

image
image710×205 6.14 KB

9

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.