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

    YOY QoQ Mom

    dinesh punnam

      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
          Jim Dehner

          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
            dinesh punnam

            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
              Jim Dehner

              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
                dinesh punnam

                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
                  Jim Dehner

                  sorry

                  sure someone else will be able to help you out

                  • 6. Re: YOY QoQ Mom
                    Shinichiro Murakami

                    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
                      dinesh punnam

                      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
                        Shinichiro Murakami

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

                         

                        Thanks,

                        Shin

                        • 9. Re: YOY QoQ Mom
                          Shinichiro Murakami

                          attaching the file.

                           

                          Shin

                          • 10. Re: YOY QoQ Mom
                            dinesh punnam

                            Shin,

                             

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

                            • 11. Re: YOY QoQ Mom
                              Shinichiro Murakami

                              It may help you understand when you remove it.

                               

                              Thanks

                              Shin

                              • 12. Re: YOY QoQ Mom
                                dinesh punnam

                                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.

                                 

                                 

                                 

                                 

                                 

                                Please letme know if you need additional info in this regards.