
1. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?
Zhouyi Zhang Dec 30, 2017 4:46 AM (in response to Drony Sharma)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 ?
Jim Dehner Dec 30, 2017 5:38 AM (in response to Drony Sharma)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 ?
Mahfooj Khan Dec 30, 2017 7:38 AM (in response to Drony Sharma)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

Sample.twbx 21.3 KB


4. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?
Drony Sharma Jan 1, 2018 9:56 PM (in response to Mahfooj Khan)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.

6. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?
Zhouyi Zhang Jan 1, 2018 9:55 PM (in response to Drony Sharma)Hi, Drony
Please find my solution attached.
To achieve that, you need union your data to itself as below
and the result
Hope this could help
ZZ

% of change sample 10.4.twbx 1.2 MB


8. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?
Mahfooj Khan Jan 1, 2018 10:08 PM (in response to Drony Sharma)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:
IF DATETRUNC('quarter',[Date])={MAX(DATETRUNC('quarter',DATEADD('year',1,DATEADD('quarter',1,[Date]))))} THEN [Sales] END
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 ?
Drony Sharma Jan 2, 2018 8:15 PM (in response to Mahfooj Khan)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

DesiredView.png 46.8 KB

View1.png 110.0 KB


10. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?
Mahfooj Khan Jan 2, 2018 9:48 PM (in response to Drony Sharma)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

Qtr Sample v10.4.twbx 155.7 KB


11. Re: How to calculate % change between q4(quarter) of year 2016 and q4 of 2017 ?
Drony Sharma Jan 2, 2018 11:23 PM (in response to Mahfooj Khan)Hi Mahfooj
Thanks for your reply.
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 ?
Mahfooj Khan Jan 3, 2018 11:47 PM (in response to Drony Sharma)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 ?
Zhouyi Zhang Jan 4, 2018 4:21 AM (in response to Drony Sharma)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 ?
Drony Sharma Jan 4, 2018 5:31 AM (in response to Zhouyi Zhang)Hi Zhouyi
Thanks for your reply.
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.