3 Replies Latest reply on Dec 23, 2016 3:45 AM by Vignesh Ilangovan

    MTD & YTD with Dynamic Month Selection

    Sumeet Bedekar

      Hi Tableau Lovers,

       

      This is regarding MTD & YTD with Dynamic Month Selection.

       

      I have sample data like this-

      0.PNG

       

      And for measure I wanted to get YTD and MTD

       

      Calculated Field for YTD

      3 Calculation.PNG

      Calculated Field for MTD

      MTD.PNG

      (Note I have one parameter named as "YTD and MTD", which is used to toggle between Measure 1(YTD # of Adverse Events) and Measure2 (MTD # of Adverse Events)

       

       

      Parameter :-

      4 Para.PNG

       

      By using Calculated Field and Parameter I am getting desired result.

       

      1.PNG

      2.PNG

       

      Till now this is working perfectly the way I want (This was first requirement/view)

       

      Now here is the Climax (This is second requirement/view)

       

      I wanted to find YTD for all years i.e. 2014,2015,2016.

      This should be in sync with Date Parameter. for example if September,2016 is selected in Parameter then I want to see YTD for 2014  and 2015. In short need to get the data for 2014(Jan 2014 to Sept 2014) and 2015 (Jan 2015 to Sept 2015)


      Refer below image- Don't want to see - Sept 2014 to Nov 2014 (Blue box and X Syambol)

      f.PNG

       

      Mahfooj Khan Kinldy have a look on this.

       

       

      Regards,

      Sumeet

        • 1. Re: MTD & YTD with Dynamic Month Selection
          Sumeet Bedekar

          Here is workaround :-

           

          2.PNG

           

          As we need month number, extracted the same using Date Part from Parameter - Date

           

          3.PNG

           

          Final Output,

           

          Sept 2016 is selected,

           

          YTD - 2014 - Jan 14 to Sept 14

          YTD - 2015 - Jan 15 to Sept 15

          YTD - 2016 - Jan 16 to Sept 16

           

           

          1.PNG

          • 2. Re: MTD & YTD with Dynamic Month Selection
            Vignesh Ilangovan

            Hi Summet,

             

            In the 2014 and 2015 metric calculation add one more condition MONTH([Final Date])<=MONTH([Parameters].[date])

             

            YTD | 2015

            IF [Final Date]<=[Parameters].[date] AND  DATEDIFF('year',[Final Date],[Parameters].[date])=1

            and MONTH([Final Date])<=MONTH([Parameters].[date])

            THEN [# of Adverse Events]

            END

             

            So that for previous year month also get applied in Previous YTD

            • 3. Re: MTD & YTD with Dynamic Month Selection
              Vignesh Ilangovan

              Hi Summet,

               

              If you want in a single column then use the below logic

               

              IF ([Final Date]<=[Parameters].[date] AND  DATEDIFF('year',[Final Date],[Parameters].[date])=0 and MONTH([Final Date])<=MONTH([Parameters].[date]))

              or ( [Final Date]<=[Parameters].[date] AND  DATEDIFF('year',[Final Date],[Parameters].[date])=1 and MONTH([Final Date])<=MONTH([Parameters].[date]))

              or  ([Final Date]<=[Parameters].[date] AND  DATEDIFF('year',[Final Date],[Parameters].[date])=2 and MONTH([Final Date])<=MONTH([Parameters].[date]))

              THEN [# of Adverse Events]

              END

               

              Thanks,

              Vignesh