I have a saved search that gives me our total number of NetSuite licences in column 1 and our licences in use in column 2. I now want a third column showing the difference between the two (available licences) but I'm not sure if it's possible to reference column 1 and column 2 in my formula?
So far my saved search results look like this: Column 1: Field: Formula (Numeric) Summary type: Maximum Formula: 260 (I couldn't find a way to pull our number of total licences into a saved search so this is a manually update value each time we buy more licences)
Column 2: Field: Email Summary Type: Count
So what I'd like column 3 to look like is: Field: Formula (Numeric) Summary type: Minimum Formula: SUM({column 1} - {column 2})
But the above comes up as an invalid expression.
I've also tried: Formula: SUM({column 1}) - COUNT({Email})
Formula: SUM({Formula 1}) - COUNT({Email})
And various other combinations, but they all come up as invalid expressions.
How can I reference column 1 and column 2 in my formula to get the desired result in column 3 please?
You can't reference other columns in a saved search formula. What you can do, though, is repeat the formula from each of the columns to create a new formula. You're on the right track specifying the Summary type as MIN. MAX would work just as well, as only one value is expected from the other aggregations within the formula itself.
Field: Formula (Numeric)
Summary type: Minimum
Formula: 260 - COUNT(DISTINCT({email}))
Note: I've included DISTINCT() in the formula, as otherwise you may get the same email counted multiple times, depending on the underlying data type, filters and columns specified.