Howdy ya'll - has anybody ever had to create a sav...
# general
s
Howdy ya'll - has anybody ever had to create a saved search formula where the denominator needs to be nested in a case when statement?
👍 1
n
yes
s
How did you manage to do it??
g
Parentheses around the denominator's case statement should do it
s
I tried that and it's not working - keep getting "expression error". I think it's because I'm trying to put a summary function in the formula... i.e. sum({amount})/(nullif(sum(case when {account.number} != '4300' and {account.number} != '4500' then sum({quantity}) else 0 end),0))
Basically, we're trying to get a revenue per unit, but leave out quantities that hit shipping (account number 4300) and hit refunds (account number 4500)
So we want it to sum up all the amounts and divide it by the sum of the quantities ONLY if they don't hit one of those accounts
g
Try swapping the nullif and sum in the denominator
n
the grouping (sum) has to be the most outward parameter
sum(CASE WHEN XYZ)
s
Super appreciate the responses @Gregory Jones and @Nicolas Bean, but unfortunately I'm still getting the error when I updated the formula to: sum({amount})/(sum(nullif(case when {account.number} != '4300' and {account.number} != '4500' then sum({quantity}) else 0 end,0)))
n
give me a sec
i will try it on my end
g
My guess would be the sum around the quantity in your case statement
🔥 1
✔️ 1
❤️ 1
👍 1
💎 1
s
@Nicolas Bean you are the BOMB, thanks so much
YES
YES
YES
YES
THANK YOU SO MUCH @Gregory Jones
g
party furby
n
aha
nice work !
s
Modern versions of Ghandi, the both of ya
peanutbutterjellytime 1
g
Well that's the first time I've heard that, so I guess I'll take the rest of the day off!
🌈 1
☀️ 1
🙏 1
👑 1
Suggest double-checking the math as well....juuuuuuust in case.
s
Yep - I got it exported into Excel and double-checked a formula of what it should be versus the formula on the saved search
👍🏻 2