Nesting of aggregate functions like SUM and SUM is not allowed

Question & Answer
, , ,
1

Hello, I am getting error while creating MTD calculation.
This question is related to my previous question
Table visualization for last 7 days from yesterday
As per suggestion from @ErikG I was able to create separate visuals to generate following view
https://d1wl4jav5dp96l.cloudfront.net/original/3X/f/f/ffbc36ccffaf7abc3aeb3aa11fd4c9ab63f2caef.png
I am facing error for MTD ( from start of month to current date ) calculation for metric CVR (conversion )
Error
Nesting of aggregate functions like SUM and SUM(CASE WHEN “day” >= date_trunc(‘month’, ‘2024-02-17 20:08:11’::timestamp) AND “day” < ‘2024-02-17 20:08:11’::timestamp THEN CASE WHEN SUM(“sessions”) = 0 THEN 0 ELSE SUM(“orders”) / NULLIF(SUM(“sessions”), 0) END ELSE NULL END) is not allowed.
This calculation uses 2 calculated fields

First => c_Test_CVR
Formula =>

ifelse(
    sum(sessions) = 0, 
    0, 
    sum({total_orders})/ NULLIF(sum(sessions),0)
)

This formula works with main table. But for MTD table when I pass this calculated field to
second calculated field => c_MTD_CVR
Formula =>

periodToDateSum({c_Test_CVR}, day, MONTH, now())

When I try to save I get "Nesting of aggregate functions " error.

nesting_error
nesting_error2120×538 92.6 KB

similar formula works for other metrics, for example, for sessions => cMTD_Sessions => periodToDateSum(sessions, day, MONTH, now()) ( here is session is directly used, no need for calculated field )

c_MTD_CVR
c_MTD_CVR1920×464 71.1 KB

After removing c_MTD_CVR

C_MTD_CVR_2
C_MTD_CVR_21920×523 61.7 KB

Please help, thank you.

2

Hello @rahul.kolage !

Essentially you are asking the calculated field to do too much, and it would be best to pull the calculation apart. Before we get too far, can you breakdown why you included NULLIF(sum(sessions),0) as part of your c_Test_CVR calculated field? I want to make sure I understand your goal before I suggest something.

3

Hi @duncan, thanks for reply. NULLIF(sum(sessions),0). I put it to handle null case. But I think it is unnecessary. I will remove it from calculated field.

5

Hey @rahul.kolage !

If you get rid of that NULLIF logic, is there a reason that you can’t do something like this:

periodToDateSum(sum({total_orders}), day, MONTH, now())

And then filter the visual that you want to target to only show data when sum({sessions}) <> 0.

Let me know if this is what you are looking for!

6

@duncan
No, showing data when sum({sessions}) <> 0 was not requirement.
Issue is
"Nesting of aggregate functions like SUM and SUM is not allowed. "
I think we can’t pass Aggregate function to another when using “periodToDateSum”. May be “periodToDateSum” internally aggregates.
for example
periodToDateSum(sum({total_orders}), day, MONTH, now())

Found another function in docs which allows to pass agg. functions, that solved the issue.

periodToDateSumOverTime(sum({total_orders})/sum(sessions), day, MONTH)

Thanks for your help @duncan :+1:

1 Like
7

Hey @rahul.kolage !

I’m glad you found the solution to this one, thank you for sharing to help the community!

1 Like