11 Replies Latest reply on Aug 2, 2017 1:54 AM by Vincent C

# Latest Quarter sales Difference

Hi,

I hope you people are doing good. I have a requirement that is, I am having table like below and I want the difference between latest quarter in every year.

Eg: 2010 Q4- 2011 Q4. I am able to get all the Q4 values but I want the difference between 2012 Q4 o 2013 Q1.

May I know the possible cases in this.

 Year Quarter Sales 2010 Q1 100 2010 Q2 150 2010 Q3 251 2010 Q4 260 2011 Q1 154 2011 Q2 254 2011 Q3 352 2011 Q4 450 2012 Q1 555 2012 Q2 412 2012 Q3 325 2012 Q4 102 2013 Q1 3256
• ###### 1. Re: Latest Quarter sales Difference

like you need difference between Q4 to Q3 in this case.

• ###### 2. Re: Latest Quarter sales Difference

I don't understand requirement. What do you want compare latest quarter with?

If you want to compare last quarter with all other quarter then calculation is:

WINDOW_SUM(IF LAST() = 0 THEN SUM([Sales]) END)- SUM([Sales])

• ###### 3. Re: Latest Quarter sales Difference

I want to show every year last/latest quarter sales. means

2014Q4-2015Q4

2015Q4-2016Q4

2016Q4-2017Q1(till date difference like)

• ###### 4. Re: Latest Quarter sales Difference

This is best I can get right now

• ###### 5. Re: Latest Quarter sales Difference

Hi Habib,

I've you tried something like this?

IF FIRST() = -3 THEN 0

ELSEIF LAST() = 0 AND ATTR([Quarter]) = 'Q1' THEN SUM([Sales]) - LOOKUP(SUM([Sales]), -1) ELSE

IIF(ATTR([Quarter]) = 'Q4', SUM([Sales]) - LOOKUP(SUM([Sales]), -4), 0) END

• ###### 6. Re: Latest Quarter sales Difference

Awesome Vincent. It's really amazing. The calculation which you have sent to me exactly matched to my requirement. I did few other changes to that. Can you please share me that workbook if possible.

Thanks a lot Vincent.

• ###### 8. Re: Latest Quarter sales Difference

Sure!

As I'm currently using Tableau v10.3, you'll need at least 10.3 too.

• ###### 9. Re: Latest Quarter sales Difference

am having 10.3 only so i can use that

• ###### 10. Re: Latest Quarter sales Difference

Vincent I met a doubt. can you please helpme out from this. In the below table what if we got the another value like 2013 Q2 like. I hope you understand my issue. latest values is 2013 Q2 In this case the above calculation is not working for that Q2. can you please help me on this.

 Year Quarter Sales 2010 Q1 100 2010 Q2 150 2010 Q3 251 2010 Q4 260 2011 Q1 154 2011 Q2 254 2011 Q3 352 2011 Q4 450 2012 Q1 555 2012 Q2 412 2012 Q3 325 2012 Q4 102 2013 Q1 3256
• ###### 11. Re: Latest Quarter sales Difference

I focused on Q1 as I didn't what you really wanted to do if your last quarter wasn't Q4. So in this calculation I only do the difference between Q1 2013 and Q4 2012 if Q1 2013 is the last quarter.

If Q2 is your last quarter, what do you want to do? 1) Q2 2013 - Q1 2013? Or 2) Q2 2013 - Q4 2012?

1)

ZN( LAST() = 0 THEN SUM([Sales]) - LOOKUP(SUM([Sales]), -1) ELSE

IIF(ATTR([Quarter]) = 'Q4', SUM([Sales]) - LOOKUP(SUM([Sales]), -4), 0) END)

2)

ZN(IF LAST() = 0 THEN SUM([Sales]) - LOOKUP(SUM([Sales]), IIF(INDEX()%4 = 0, -4, -INDEX()%4)) ELSE

IIF(ATTR([Quarter]) = 'Q4', SUM([Sales]) - LOOKUP(SUM([Sales]), -4), 0) END)

This is assuming your presentation is always with Quarter as to lookup I'm using Index()%4 (so if last is Q2, then it's looking two cells above, Q3 then 3, etc).

I've modified the calculation, now it uses ZN() instead of checking if this is the first Q4 as it was necessary to have Q1 for first year.