An issue has occurred during processing. We are unable to complete the request at this time.

user033690

Hi I am new to Domo and have no SQL knowledge. I am trying to get our YTD cases quantity with some condition taken from other working beast mode. If someone can please help me figure the beast mode below why it's erroring.  Thank you ion advance.

 

SUM(CASE WHEN `Year`=2020 AND `Month #` <=`Month # for Today`
THEN (SUM(CASE
WHEN `Unit price`>100 THEN ((`SO Ordered Qty`)*`Cases per Pallet`)
ELSE (`SO Ordered Qty`)
END))
ELSE 0
END)

MarkSnodgrass

I would get rid of your outer SUM function as I think it not necessary since you are already summing inside your case statement. I would suggest this:

CASE WHEN `Year`=2020 AND `Month #` <=`Month # for Today`
THEN (SUM
        (CASE WHEN `Unit price`>100 THEN (`SO Ordered Qty`*`Cases per Pallet`)
        ELSE `SO Ordered Qty`
        END)
        )
ELSE 0
END

GrantSmith

HI @user033690 

 

For your specific beast mode it's failing because you're attempting to aggregate on top of an aggregate which isn't possible. When starting a problem like this it's good practice to start breaking these out into smaller chunks to validate the data as you're working through it. 

 

First thing you'll want to do is instead of hard coding 2020 in your beast mode you're wanting the current year. As is it will break when January 2021 comes around and you'd need to keep updating this beast mode every year.

So instead of 2020 you can get the year of the current date:

YEAR(CURRENT_DATE())

 

Depending on how you're wanting to display this information depends on how to solve your problem. If you're just getting a text card with a single value or using at a summary number this should work:

Essentially this is combining your filter conditions into a single CASE statement instead of trying to do sum of sum twice.

 

 

If you're wanting a running YTD total then you'll need to utilize a Window Function (this requires a feature switch turned on. Talk to your CSM if you don't have it enabled yet).

 

SUM(SUM(CASE WHEN `Unit price`>100 THEN 
    (`SO Ordered Qty`)*`Cases per Pallet`
ELSE
    (`SO Ordered Qty`)
END
))
OVER (PARTITION BY `Year`)

 

(these are all untested code examples but should work)

 

MarkSnodgrass

I would get rid of your outer SUM function as I think it not necessary since you are already summing inside your case statement. I would suggest this:

CASE WHEN `Year`=2020 AND `Month #` <=`Month # for Today`
THEN (SUM
        (CASE WHEN `Unit price`>100 THEN (`SO Ordered Qty`*`Cases per Pallet`)
        ELSE `SO Ordered Qty`
        END)
        )
ELSE 0
END

GrantSmith

HI @user033690 

 

For your specific beast mode it's failing because you're attempting to aggregate on top of an aggregate which isn't possible. When starting a problem like this it's good practice to start breaking these out into smaller chunks to validate the data as you're working through it. 

 

First thing you'll want to do is instead of hard coding 2020 in your beast mode you're wanting the current year. As is it will break when January 2021 comes around and you'd need to keep updating this beast mode every year.

So instead of 2020 you can get the year of the current date:

YEAR(CURRENT_DATE())

 

Depending on how you're wanting to display this information depends on how to solve your problem. If you're just getting a text card with a single value or using at a summary number this should work:

Essentially this is combining your filter conditions into a single CASE statement instead of trying to do sum of sum twice.

 

 

If you're wanting a running YTD total then you'll need to utilize a Window Function (this requires a feature switch turned on. Talk to your CSM if you don't have it enabled yet).

 

SUM(SUM(CASE WHEN `Unit price`>100 THEN 
    (`SO Ordered Qty`)*`Cases per Pallet`
ELSE
    (`SO Ordered Qty`)
END
))
OVER (PARTITION BY `Year`)

 

(these are all untested code examples but should work)