6 Replies Latest reply on Nov 28, 2016 12:17 PM by Tom W

How to have the % change in a table?

Hi,

I am having the data set as below, the upper table in the picture below. In Tableau, I have 1 dimension - Period, and 3 Measures - Sales, Spend, Traffic. CY is Current Year; LY is Last Year.

I want to achieve a Tableau table in the format of the lower part of the screenshot, where it sums Sales, Spend, Traffic in LY and CY, and have LY Var as (CY-LY)/LY - the percentage change of Current Year (CY) from Last Year (LY).

Any idea?

Right now, I manually created another 3 rows in Excel following LY, LY Var, and calculated the difference. But then in the Tableau table, it will only show the numeric different, not the % difference. If I calculated the % difference in Excel, then LY Var % difference will be summed up, because Sales, Spend, Traffic for LY and CY are summed. LY Var % difference should be averaged, not summed. • 1. Re: How to have the % change in a table?

Hi Chris,

Can you please upload/attach a Tableau Packaged Workbook so we can get a better look at your data structure?

I'd suggest you should be able to achieve this by creating a calculated field like;

(SUM([CY])-SUM([LY])/SUM([LY]))

• 2. Re: How to have the % change in a table?

Hi Tom,

Here you go! I would like to have a third column LY VAR that is calculated as (CY-LY)/LY. For example, LY Var for Sales will be (25-15)/15 = 66.7%

Thanks!.

• 3. Re: How to have the % change in a table?

Hi Chris,

The problem is that you're effectively trying to create a new dimension member which would be 'LY Var' in the Period dimension.

It would be much easier to achieve this if your data structure was structured like;

Metric, ValueLY,ValueTY

or even better

Metric, Period, Value

Would this be possible?

• 4. Re: How to have the % change in a table?

You mean:

Metric Period Value

Sales LY 3.5

Spend LY 4

....

etc.?

I was hoping there is other workaround besides data manipulation. If not, I will probably resort to data transfomration...

• 5. Re: How to have the % change in a table?

Here is the sample workbook in TD 9.2. I would like to have a third column LY VAR that is calculated as (CY-LY)/LY. For example, LY Var for Sales will be (25-15)/15 = 66.7%

Thanks!.

• 6. Re: How to have the % change in a table?

The best structure would be Metric, Period, Value as per your example.

However, you can get part way there by using the inbuilt pivot functionality in Tableau - Pivot Data from Columns to Rows

Connect to your data, select your three metric columns, right click and select pivot.

Now in your report create three calculated fields;

CY = sum(if [Period]='CY' then [Pivot Field Values] end)

LY = sum(if [Period]='LY' then [Pivot Field Values] end)

LYChange = ([CY]-[LY])/[LY] 1 of 1 people found this helpful