9 Replies Latest reply on Jul 4, 2012 11:43 PM by Anurag Gulati

# Calculating Year on Year Growth

Hi Guys,

I am stuck with this issue in Tableau. I want to calculate year on year growth and then consolidate them by Industry. I am using the formula

(Current Year Sales -Previous Year Sales)/Previous Year Sales

I created a custom calculation and when I added it to Measure Values the percentage growth shows 1000% even though there was a decline in the figures. My question is how to create a proper Calculation and then add it to measures?

Thanks

Anurag

• ###### 2. Re: Calculating Year on Year Growth

Hi Anurag,

Here's an example using the Superstore sales data.

Jonathan

• ###### 3. Re: Calculating Year on Year Growth

Hi Jonathon -

I am using your example to ask if this can be used with a continuous date line (I have this problem in a separate post).

I have taken your calculation and tried to show this a line over time...but cannot seem to do it.

Any thoughts?

• ###### 4. Re: Calculating Year on Year Growth

Hi Michael,

Having both the discrete (blue) YEAR(Order Date) on the Level of Detail shelf in the view causes Tableau to return a discrete result for each Year(Order Date) and it won't connect the lines unless we tell Tableau how to. Therefore, you have two options:

1) remove the discrete YEAR(Order Date) from the Level of Detail shelf and set your table calcs to compute using Table(Across). That's in the "simplest" worksheet in the attached.

2) Ctrl+drag the blue YEAR(Order Date) from the Level of Detail shelf to the Path shelf. This will tell Tableau how to connect the points and get you a line. Then set the Compute Using for both your table calcs to Order Date. That's in the "more complicated" worksheet in the attached.

Jonathan

• ###### 5. Re: Calculating Year on Year Growth

Hi Jonathan, I think you attached the wrong file?

I don't see anything with "more complicated" and "simplest"...

Mike

• ###### 6. Re: Calculating Year on Year Growth

Sorry, wrong version, try this one.

• ###### 7. Re: Calculating Year on Year Growth

I have tried to work with the example, but still cannot get the correct growth measure. It seems that may be its is to do with data. I have added some sample data to the attached file. My custom calculation is done in "Growth" measure where I am using the formula

(x-y)/y

to calculate Growth of x over y but the Growth percentage tableau calculates is really high and incorrect. I also tried to add a Table Calculation where i do not get a Table (Across) option when using Quick Table Calculation and YTD Growth Year on Year Growth are greyed out.

In the attachment

Raw Data file

Test.twbx

• ###### 8. Re: Calculating Year on Year Growth

Hello Anurag,

The problem you experience is due to the way Tableau is calculating your formula. As it is defined as a direct ratio of the fields it is calculated at the level of each individual record. Thus it works fine on the record level, but then when you aggregate many rows into one category, you are getting nonsense - i.e. you are adding percentages, or taking average, etc. In order to make this work you should make it aggregate first all rows and then divide to get the percentage growth. I.e. instead of (x-y)/y the correct formula is (sum(x)-sum(y))/sum(y). I made exactly that modifying your formula in the attached workbook.

Hope this helps,