2 Replies Latest reply on Aug 17, 2016 8:11 AM by Derrick Austin

Any way to simplify this crazy calculation?

When I first started learning Tableau, I went through all of the calculations in all of my workbooks to try and understand what was happening. While there were plenty that were easy to follow and understand (things like COUNTD(FacilityID) to show how many facilities there are), there were some that just made me want to laugh maniacally while huddling in a corner. This was the first calculation that incapacitated my brain:

IF MIN([Invoice Date])<DATE("7/1/2015") AND MIN([Sales Category])<>'Opportunity'

THEN (ZN(SUM([Net Sales]))-LOOKUP(ZN(SUM([Net Sales])),-1))/ABS(LOOKUP(ZN(SUM([Net Sales])),-1))

ELSEIF MIN([Invoice Date])<DATE("7/1/2015") AND MIN([Sales Category])='Opportunity'

THEN (ZN(SUM([Net Sales]))-LOOKUP(ZN(SUM([Net Sales])),-1))/ABS(LOOKUP(ZN(SUM([Net Sales])),-1))*-1

ELSE Null

END

This calculation, named ColorPctGrowth, is used in another calculation named ColorBucket:

IF [ColorPctGrowth}>=.5 THEN 2

ELSEIF [ColorPctGrowth]>=.15 AND [ColorPctGrowth]<.5 THEN 1

ELSEIF [ColorPctGrowth]<=-.15 AND [ColorPctGrowth]>=-.5 THEN -1

ELSEIF [ColorPctGrowth]<-.5 THEN -2

ELSE 0

END

ColorBucket is used to assign color based on year over year growth. If the growth change is within 15% there's no coloring; 15%-50% change is lightly colored; greater than 50% is darkly colored. Still, I just can't shake the idea that this could be done much simpler than the calculations currently being used. If anyone speaks calculations fluently, could you help me out? Is there an easier way to accomplish this, or am I stuck with this behemoth?

• 1. Re: Any way to simplify this crazy calculation?

There 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

1 of 1 people found this helpful
• 2. Re: Any way to simplify this crazy calculation?

There 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.

1 of 1 people found this helpful