1 2 Previous Next 19 Replies Latest reply on Jan 6, 2018 12:01 AM by Drony Sharma

# How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Everyone.

Wish you all a very Happy New Year 2018!

Please help me to solve this question. I need to calculate the %change, difference between quarter 4 of 2016 and quarter 4 of 2017 . As shown below, I have Sum(Sales) for different peer groups for last 5 quarters. I need to calculate the %change in sales between  2017 q4 and 2016 q4.

 Peer Group Date 2016 Q4 2017 Q1 2017 Q2 2017 Q3 2017 Q4 %change (2017 Q4 - 2016 Q4) A 12 45 13 45 26 B 34 67 15 42 97 C 56 89 92 93 21 D 78 20 35 34 68

Thanks for your time.

• ###### 1. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi, Drony

can you provide sample workbook or sample data?

ZZ

• ###### 2. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Good morning - if your data is structured as a date dimension and a measure similar to superstore data you can use the formula below

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

you would construct the view as show below If your data has a different structure you will need a different solution - please attache a twbx workbook

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Drony,

Find my approach below,

I've created dummy data to replicate your use case. I'm taking only 1st date of each quarter here. Lets assume you've data like this. Create few calculated fields like this

To get Current Qtr sales use below calculation

IF DATETRUNC('quarter',[Date])={MAX(DATETRUNC('quarter',[Date]))} THEN [Sales] END

Last Year Same Qtr

IF DATETRUNC('quarter',[Date])={MAX(DATETRUNC('quarter',DATEADD('year',-1,[Date])))} THEN [Sales] END

To check whether the calculations are correct or not, drag these fields on canvas and check Now we can get the % Change like this

(SUM([Last Year Qtr])-SUM([Current Qtr]))/SUM([Current Qtr])

Here is the result Workbook v10.4 attached for reference. Let us know if this help.

Happy New Year :-)

Mahfooj

3 of 3 people found this helpful
• ###### 4. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Mahfooj

Wish you too a very happy new year :-)

Thank you so much for your prompt reply. Really appreciate it.

I have chosen Sample Superstore data set  for this particular problem. In this case, if we filter 'Shipping date' for last 6 quarters, then we get 2018 Q1(which is our current quarter now). But we can't use 2018 Q1 sales in our calculation as it is incomplete because quarter has just started.

So what i am trying to do is, to calculate the %change in 2017 Q4 and 2016 Q4.

According to your previous reply, you have created a calculated field "current qtr" which returns the sales of current quarter. In my case i am not taking the current quarter for the %change calculation.

I have attached the screenshot for your reference. I look forward to your reply. Thanks for your time.

• ###### 5. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Zhouyi

I am sorry, i don't know how to attach the .twbx file.

So i have attached the screenshot for your reference. Here i am using the Sample Superstore data set. Thanks.

• ###### 7. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Drony,

Follow this thread to attach packaged workbook.(**.twbx)

Tableau Forum Guidelines

Mahfooj

• ###### 8. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Drony,

Give a try, little change in both the calculation

Current Qtr:

IF DATETRUNC('quarter',[Date])={MAX(DATEADD('quarter',-1,[Date]))} THEN [Sales] END

Last Year Qtr:

Let us know if this help or share the sample workbook.

Mahfooj

• ###### 9. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Mahfooj

Thanks for your time.

I have created three calculated fields now as you advised.

1. Current Qtr

IF DATETRUNC('quarter',[Ship Date])={MAX(DATEADD('quarter',-1,[Ship Date]))} THEN [Sales] END

2. Last Year Qtr

IF DATETRUNC('quarter',[Ship Date])={MAX(DATETRUNC('quarter',DATEADD('year',-1,DATEADD('quarter',-1,[Ship Date]))))} THEN [Sales] END

3. % Change

(SUM([Last Year Qtr])-SUM([Current Qtr]))/SUM([Current Qtr])

Now i am trying to drag the %change field on Columns, because i want to have a different column in the view/crosstab which shows the % change. But while doing this i am not getting the desired output in the vie

I am using sample superstore workwook, the saved dataset in tableau desktop. This one is very large to attach. I am sorry for that. Therefore i have attached the screenshot of my tableau desktop view.

Thanks for helping me:-)

Regards:

Drony

• ###### 10. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Drony,

I don't think the way you're expecting the output will be easy to get in a single view.

find my different approach using table calculations

workbook v10.4 attached for your reference. Let us know if this help.

Mahfooj

• ###### 11. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Mahfooj

I have already tried this table calculation before, but this is not the expected output. Please suggest any alternative method if that is possible to generate the desired output.

Thanks..

• ###### 12. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Drony,

As per my knowledge you can't achieve the expected output in a single view. To achieve the output you've to use multiple worksheets in a dashboard. One sheet for your historical quarter sales and another sheet for your % change. But I won't recommend this approach.

May be someone else can take a look in your requirement and come up with some solution.

Mahfooj

• ###### 13. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi, Drony

Have you tried my solution by union the data to itself?

ZZ

• ###### 14. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?

Hi Zhouyi

I am able to create a union with same table i.e Orders union with Orders1 as shown in your first screenshot.

I am sorry i couldn't understand the next step provided in your second screenshot. If i try to add the Table name (Orders1) in my column shelf, It comes in red color (an error) and what is the calculated field that you have put into text mark.

Kindly elaborate more to proceed further.

FYI...

I am using Tableau desktop 10.3.

Thanks for your time. elaborate next step to proceed further.

1 2 Previous Next