
1. Re: Any way to simplify this crazy calculation?
Derrick Austin Aug 17, 2016 7:53 AM (in response to Vincent Baumel)1 of 1 people found this helpfulThere are probably several ways to simplify  but an easy win is the "LOOKUP(ZN(SUM([Net Sales])),1)" chunk.
I see it used 4 times. If you moved it to it's own calculated field and then reference that instead, you are instantly much cleaner and simpler.
Additionally, you never need "ELSE Null". If no condition is met, the IF statement automatically returns NULL, so you are simply duplicating effort here.
Hope this helps!
 Derrick

2. Re: Any way to simplify this crazy calculation?
Derrick Austin Aug 17, 2016 8:11 AM (in response to Derrick Austin)1 of 1 people found this helpfulThere is also a random extra set of () hanging out around the ZN's.
You can drop those and do the other updates to end up with something like this:
IF MIN([Invoice Date]) < DATE("7/1/2015") AND MIN([Sales Category]) <> 'Opportunity'
THEN ZN(SUM([Net Sales]))  [Previous Sales]) / ABS([Previous Sales])
ELSEIF MIN([Invoice Date]) < DATE("7/1/2015") AND MIN([Sales Category]) = 'Opportunity'
THEN ZN(SUM([Net Sales]))  [Previous Sales]) / ABS([Previous Sales])*1
END
Depending on how much you use it, it might also make sense to abstract the min invoice date and/or opportunity category.
If so, you might end up with something as simple as this:
IF [Started Before July 2015] AND NOT [Is Opportunity]
THEN ZN(SUM([Net Sales]))  [Previous Sales]) / ABS([Previous Sales])
ELSEIF [Started Before July 2015] AND [Is Opportunity]
THEN ZN(SUM([Net Sales]))  [Previous Sales]) / ABS([Previous Sales])*1
END
And this is without even changing the logic. Looking at the logic, there could be many ways of simplifying. For example, do you need opportunities to be colored differently? Perhaps you can drop the IF statement and color on a new YoY Growth field combined with Sales Category.
Are all conditions without [Started Before July 2015] excluded? If so, you can move that MIN([Invoice Date] to a filter.
There are definitely lots of areas to expand there as well, depending on the backend logic needed.