Howdy ya'll - has anybody ever had to create a sav...
# generals
Sirus Badiee
11/21/2019, 7:04 PMHowdy 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
Nicolas Bean
11/21/2019, 7:07 PMyes
s
Sirus Badiee
11/21/2019, 7:12 PMHow did you manage to do it??
g
Gregory Jones
11/21/2019, 7:23 PMParentheses around the denominator's case statement should do it
s
Sirus Badiee
11/21/2019, 7:45 PMI 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))
Sirus Badiee
11/21/2019, 7:46 PMBasically, 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)
Sirus Badiee
11/21/2019, 7:46 PMSo 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
Gregory Jones
11/21/2019, 7:56 PMTry swapping the nullif and sum in the denominator
n
Nicolas Bean
11/21/2019, 7:57 PMthe grouping (sum) has to be the most outward parameter
Nicolas Bean
11/21/2019, 7:57 PMsum(CASE WHEN XYZ)
s
Sirus Badiee
11/21/2019, 7:59 PMSuper 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
Nicolas Bean
11/21/2019, 8:00 PMgive me a sec
Nicolas Bean
11/21/2019, 8:00 PMi will try it on my end
g
Gregory Jones
11/21/2019, 8:00 PMMy guess would be the sum around the quantity in your case statement
🔥 1
✔️ 1
❤️ 1
👍 1
💎 1
s
Sirus Badiee
11/21/2019, 8:00 PM@Nicolas Bean you are the BOMB, thanks so much
Sirus Badiee
11/21/2019, 8:01 PMYES
Sirus Badiee
11/21/2019, 8:01 PMYES
Sirus Badiee
11/21/2019, 8:01 PMYES
Sirus Badiee
11/21/2019, 8:01 PMYES
Sirus Badiee
11/21/2019, 8:01 PMTHANK YOU SO MUCH @Gregory Jones
g
Gregory Jones
11/21/2019, 8:02 PMparty furby
n
Nicolas Bean
11/21/2019, 8:02 PMaha
Nicolas Bean
11/21/2019, 8:02 PMnice work !
s
Sirus Badiee
11/21/2019, 8:03 PMModern versions of Ghandi, the both of ya
peanutbutterjellytime 1
g
Gregory Jones
11/21/2019, 8:03 PMWell 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
Gregory Jones
11/21/2019, 8:04 PMSuggest double-checking the math as well....juuuuuuust in case.
s
Sirus Badiee
11/21/2019, 8:05 PMYep - I got it exported into Excel and double-checked a formula of what it should be versus the formula on the saved search
👍🏻 2