12 Replies Latest reply on Jun 12, 2018 12:21 PM by dinesh punnam

# YOY QoQ Mom

Hello All,

I saw multiple threads on Yoy calculations  but couldn't find something similar but please look at  my question and let me know

I have to calculate

1)YoY (Last year vs this year)

2)QoQ  (last year  quarter vs this quarter same quarter ) and

3) Mom (Last year month vs this year same month )  Calculations but trickiest part

a) Year, quarter and month are filters (they are not in view , view will have other dimension in place like sales office level  ) -- based on selections the growth should be calculated

b) let's say if we are doing quarterly comparisons (Q2 2017 r vs Q2 2018 ) some time we will not have data full for quarter  and we have only for "April" in 2018 Q2  then can we  ask tableau to figure that out and compare only with April of last year  not compare April data with q2 of last year ???---- saying we have monthly level data (not daily )

am attaching sample workbook - any input is appreciated.

Thanks,

Dinesh

• ###### 1. Re: YOY QoQ Mom

see the attached superstore example

it returns this

all  the formula are based on today if you don't want to use today() as the basis you would use a parameter and revise the formulas subbing the parameter name for today()

the remaining formula follow the same pattern and are in the wokbook

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.

• ###### 2. Re: YOY QoQ Mom

Thanks Jim for your input !!

but above calculations will not help in addressing below

I have to calculate below  based on below filter

1)YoY (Last year vs this year) when year is selected in the filter

2)QoQ  (last year  quarter vs this year same quarter ) when quarter is selected for that year (Quarter filter )

3) Mom (Last year month vs this year same month ) when month is selected for that year or quarter ( Month filter )

Thanks,

D

• ###### 3. Re: YOY QoQ Mom

ok - let me understand this - you have filters that are not in the view "Year, quarter and month are filters "they are not in view , view will have other dimension in place like sales office level  " so you are passing them through some action or you have applied the filter to this particular sheet from another sheet?

Or is it your intent to place 3 separate date filters on the viz and have them work collectively? and have the user set 3 different filters in place of 1 parameter that looks the same

• ###### 4. Re: YOY QoQ Mom

Jim,

Please find my responses under lined - thanks much for your time,

You have filters that are not in the view "Year, quarter and month are filters "they are not in view , view will have other dimension in place like sales office level  " so you are passing them through some action or you have applied the filter to this particular sheet from another sheet? --

Year, quarter and month are derived from same field cuz user want those selections and they will be in filters section but not in view

You are right on second point --

Or is it your intent to place 3 separate date filters on the viz and have them work collectively? and have the user set 3 different filters in place of 1 parameter that looks the same

I have to have separate filters that works collectively (so that they can choose specific month for selected year and quarter )  these date fields will be in  filters but not in view -  please let me know if you not clear , thanks much again,

Dinesh

• ###### 5. Re: YOY QoQ Mom

sorry

• ###### 6. Re: YOY QoQ Mom

HI Dinesh

It's had to explain everything, but could you check the attachment and if it seems working , could you reverse-eng it.

Thanks,

Shin

• ###### 7. Re: YOY QoQ Mom

Thanks much Shin - Appreciate your time !!

I did a quick validation by adding segment (below pic) to the view and the sales are getting duplicated - My ultimate aim is to pull other dimensions into view  and calculate the Yoy (%) 's ...

FYI - Only catch here is what ever the user  select  (year,quarter or month ) it has go back on year back and calculate the sales of that period and then calculate  the percentages.

• ###### 8. Re: YOY QoQ Mom

Need to add this dimension (Segment) to all "LOD" calculations. (=which include "fixed" ).

Thanks,

Shin

• ###### 9. Re: YOY QoQ Mom

attaching the file.

Shin

• ###### 10. Re: YOY QoQ Mom

Shin,

Thanks much - quick question ? Why are we using index here ?

• ###### 11. Re: YOY QoQ Mom

Thanks

Shin

• ###### 12. Re: YOY QoQ Mom

Thanks shin,

I am trying to do reverse engineering but am not able to get right result set - looks like i am confused little bit ...

I am attaching my Workbook with 3 three sheets and could you please help me in this regards

1) I am trying to get % AUR  ( GR/STD) change between years ( please direct me with years, i will do for quarters and months)

2) I am unable to achieve CY AUR , prior year AUR using your method without years being in view

3) I am trying to achieve (pic 2) yoy%AUR for sales office and adv great industry separately.