on a saved search, grouped by customer. I want to ...
# suiteanalyticsj
JBL
05/27/2021, 2:06 AMon a saved search, grouped by customer. I want to have a calculation like: sum({tranestgrossprofit})/sum({netamountnotax}) to estimage the margin of transactions. but i can't work out how to do this. any pointers?
k
KevinJ of Kansas
05/27/2021, 2:28 AMsum({transaction.desiredField}/nullif(sum({transaction.otherfield},0))
KevinJ of Kansas
05/27/2021, 2:28 AMmake your "grouping" type maximum.
j
JBL
05/27/2021, 2:29 AMah thanks
JBL
05/27/2021, 2:29 AMI'll give that a go
k
KevinJ of Kansas
05/27/2021, 2:30 AMyou might have to play with your transaction selection criteria on your criteria tab so that it doesn't include transactions you don't want.
KevinJ of Kansas
05/27/2021, 2:30 AM(also to help performance)
j
JBL
05/27/2021, 2:30 AMrighto, thanks kevin
k
KevinJ of Kansas
05/27/2021, 2:30 AMShould be noted - NS when you do stuff like that doesn't like "mixed" levels of aggregation. Everything has to be summarized in order for calucations to work.
KevinJ of Kansas
05/27/2021, 2:31 AM(i.e you can't do a sum({field}/{field2}
KevinJ of Kansas
05/27/2021, 2:32 AMalso, i missed a parenthesis in my forumula before the "/"
KevinJ of Kansas
05/27/2021, 2:32 AMso
sum({transaction.desiredField})/nullif(sum({transaction.otherfield},0))j
JBL
05/27/2021, 3:01 AMthe problem is that sum is not available on the formula field area
JBL
05/27/2021, 3:01 AMmessage has been deleted
JBL
05/27/2021, 3:01 AMsee the highlighted area above
k
KevinJ of Kansas
05/27/2021, 3:02 AMlooks like you're missing your close parenthesis
j
JBL
05/27/2021, 3:11 AMgood catch thanks