5 Replies Latest reply on Mar 19, 2018 7:59 AM by pavani.p.1

    Calculate YTD,YOY,MOM using Filters and without Parameters

    pavani.p.1

      Hi

       

      Attached is the sample workbook where you can see YTD,YOY,MOM calculated using Year and Month Parameters.

       

      But my requirement is to use Order Month(i.e., 1st day of month) as filter to dynamically show YTD,YOY and MOM.

       

      Note: I wanted this to be in Tableau version 10.4.4  or less

       

      Few reasons why I don't want to use parameters and stick to only filters

       

      1) Every time when I open my workbook I wanted to have default view to be the most current month (which is not possible using Parameters as these are static and cannot default to latest month)

      2) For Example if we have year parameter with values (2014,2015,2016,2017,2018) and Month parameter with values 1-12 and when we select Year 2018, Logically we wanted to see only available months Jan and Feb  in Month Parameter but that wont be possible using parameters.

       

      Please help me if we can do the attached workbook using filters instead of Parameters.

       

      Thanks in Advance

       

      PP

        • 1. Re: Calculate YTD,YOY,MOM using Filters and without Parameters
          Jayshree Dawrewar

          Hi Pavani,

           

          For your first query, that is to keep current date / month as default whenever you open your workbook:

           

          1. [Cal Date] = { FIXED : MAX([Cal Date])}  // this is on date level, you can append month function and apply for max month in your data

           

          Create this calculated field and use it as quick filter with True as checked. So by this you will get always true i.e. max date/month data.

           

          2. For the selected year to show available months for that year:

           

                    a. Apply filter for Year and Month separately

                    b. Then select only relevant values for Month filter

                             

                    c. So now for any selected year you can see its respective or valid months in data:

                             

                                  

           

          And there will be no need to create any parameters for the same.

           

           

          Hope this Help!

           

           

          Regards,

          Jayshree Dawrewar

          5 of 5 people found this helpful
          • 2. Re: Calculate YTD,YOY,MOM using Filters and without Parameters
            Tushar  More

            Hi Pavani,

             

            In addition to directions provided by Jayshree Dawrewar please find my approach based on built-in latest date functionality.

             

            Hope this helps.

             

            ~Tushar

            1 of 1 people found this helpful
            • 3. Re: Calculate YTD,YOY,MOM using Filters and without Parameters
              pavani.p.1

              HI Jayashree,

               

              Thanks for the response. Sorry if I have confused you.Let me Clarify my question.

               

              First Question:

              Is there a way where following two can work using Parameters?

               

              a) Every time when I open my workbook I wanted to have default view to be the most current month.

              b) For Example if we have year parameter with values (2014,2015,2016,2017,2018) and Month parameter with values 1-12 and when we select Year 2018, Logically we wanted to see only available months Jan and Feb  in Month Parameter. will that be possible when am using parameters?

              Please refer to the workbook attached in the initial discussion.

               

              If the answer for the first question is No, then I will go with question 2.

               

              Second Question:

              If the Q1 doesnt work then I wanted to show YTD, YOY,MOM using filters. How can I calculate YTD,YOY and MOM using filters (and with no parameters)

              1 of 1 people found this helpful
              • 4. Re: Calculate YTD,YOY,MOM using Filters and without Parameters
                Jayshree Dawrewar

                Hi Pavani,

                 

                 

                I am not able to open the workbook at my end.

                 

                But if you can achieve First Question using filters then why you need parameters?

                 

                 

                Regards,

                Jayshree

                1 of 1 people found this helpful
                • 5. Re: Calculate YTD,YOY,MOM using Filters and without Parameters
                  pavani.p.1

                  Jayshree Dawrewar

                   

                  My original requirement is to calculate YOY,YTD,MOM for given month selected by user.

                   

                  I already have done 90% of my work using Parameters. Only thing left is to always display default current month and to show only relevant months for the year 2018.(I know this can be achieved using filters but I cannot apply filters on top of my exisitng Parameter calculations)

                   

                  If I use filters and redo all my work,  I can show default current month in display and relevant months for year 2018 but can never calculate YTD,YOY,MOM since data is always limited to given month selected by user.

                   

                  Not sure why you cannot able to open my workbook. Attached my workbook again.