We were recently working on a visualization and wanted to include period over period growth rates. We used the table calculation for Percent Difference From to show the change from one period to the next. We used the same calculation on the color, so positive changes would be one color and negative changes would be a different color. An issue came up when we noticed that some products were showing a negative growth rate, even though the period over period growth was positive. This was caused by the fact that the sales in the first period were negative. The table calculation does not take into account the fact that sales in one period may be negative. For example, if sales in Q1 were -$500 and in Q2 sales were $1000, the growth rate should be 300%. But because the table calc is written as:

SUM([SALES]) / LOOKUP(SUM([SALES]),-1) - 1 OR (1000 / -500) - 1 = -300%

Obviously, growth from a negative to a positive is positive growth. So we had to adjust the formula to ensure the correct growth rate was represented. The solution we came up with is the following formula:

IF LOOKUP(SUM([SALES]),-1)>0

THEN SUM([SALES]) / LOOKUP(SUM([SALES]),-1) - 1

ELSE (SUM([SALES]) + ABS(LOOKUP(SUM([SALES]),-1))) / ABS(LOOKUP(SUM([SALES]),-1))

END

The calculation results in:

(1000 + 500) / 500 = 300% growth rate.

Is this the cleanest way to handle the issue?

Sean

This content has been marked as final.
Show 0 replies

Create your Account

Become a Viz Whiz on the Forums!

Support the Community and master Tableau.