4 Replies Latest reply on Feb 17, 2019 7:57 PM by anoosha shetty

    Last month and current month calculation at month level

    anoosha shetty

      Hi Experts ,

       

      I am very new to tableau and any idea is really appreciated

       

      My use case :

      user wants to have ability to select if he wants to see last year , current year values , YOY % along  the date granularity (daily, weekly/monthly/yearly)  and last week and current week  with WOW %  along date granularity (daily, weekly) and last month , current month , MOM% metrics along only monthly granularities

       

      While having date slider to choose any date range they want to see all this metrics on

       

      Something like this below :-

      Screen Shot 2019-02-14 at 10.53.20 PM.png

       

      So in the above screenshot , the chart should dynamically change when user select YOY across different date granularity (daily, weekly/monthly/yearly)

      And also same chart should show values for last month instead last year  at month level and WOW (along with last week and current week )at daily and weekly level

       

       

      So far I have got the YOY metrics across daily/weekly/monthly /yearly granularities

      1. created a parameter called(date granularity)
      2. Based on above parameter created a calculated field as “date type selected”
      3. I have create  one more date called "adj date" which will be used as slider , and it will have corresponding local dates (both previous and current values for a given adj dates)
      4. Have got another calculated field called “period” to identify that the selected date belong to current year or last year
      5. By Moving 'date type selected' field in to x axis , adj date in to filter , and my visit metrics in the  Y axis , and pulling the period into colour.

       

       

      Attached is the Workbook has  the graph which user similarly wants for the MOM , WOW metrics in the single dashboard   ,

      I am facing issues while implementing the same to get  MOM % by last month and current month values ! Can someone take look at this workbook and help me out

       

      Thanks,

      anoosha

        • 1. Re: Last month and current month calculation at month level
          David Maning

          Hi Anoosha,

           

          I am not sure if I understood you correctly. However this logic will help you to solve this puzzle.

           

          1) We should normalize our dates:

          IF DATEDIFF('year', [local Dt], {MAX([local Dt])}) = 1

          THEN DATEADD('year', DATEDIFF('year', [local Dt], {MAX([local Dt])}), [local Dt])

          ELSE [local Dt]

          END

           

          2) Show two consequential year:

          DATEDIFF('year', [local Dt], {MAX([local Dt])}) <= 1

           

          3) YTD:

          IF

              MONTH([local Dt]) <= MONTH({MAX([local Dt])})

              AND

              DAY([local Dt]) <= DAY({MAX([local Dt])})

          THEN 'Show'

          END

           

          4)  CY value

          SUM(IF YEAR([local Dt]) = YEAR({MAX([local Dt])})

          THEN [Visits]

          END)

           

          5) PY value

          SUM(IF YEAR([local Dt]) != YEAR({MAX([local Dt])})

          THEN [Visits]

          END)

           

          6) Change

          [_Visits_CY] / [_Visits_PY] - 1

           

          Please mark my response as correct and helpful.

           

          Worksheet as attached.

           

          Trust this helps.

          D

          • 2. Re: Last month and current month calculation at month level
            anoosha shetty

            Hi David Maning

             

            I am not sure why I cant open the workbook you have attached (32.3KB) is there.  Here is the error I am getting when I try to open it

            Screen Shot 2019-02-15 at 3.31.55 PM.png

             

             

            What I was trying to explain above is:-

             

            Problem statement :-

            I need to calculate last year , current year value at the date granularities daily, weekly , monthly, yearly level . with the date slider provided to the user to select the range of dates .

            So when user select the range of dates in the date slider and then the date granularity  what he wants to see  , my  X axis in the chart should dynamically change , along with the aggregation values of Last year and current year calculations

             

            Solution I have tried so far :-

            Please refer to the tab "YOY" in the attached workbook .

            mock up of the graph user wants is  here:-

            Screen Shot 2019-02-15 at 4.20.54 PM.png

             

            But currently I have 2 extra asks :

            • But my problem now is user wants the similar calculations for  MOM to see the last month value and current month at the "monthly" granularity . And WOW to see the last week and current week at the "daily" and "weekly" granularity
            • And all of these  YOY , MOM , WOW metrics to be shown   is in the single tab of the dashboard.

             

             

            I also tried your approach but I seem to get only CY values for 2019 local dt and PY value shown against 2018 local dt. Please check the tab "David" where I have tried your solution let me know where I am going wrong !

             

            Screen Shot 2019-02-15 at 4.07.44 PM.png

             

             

             

            Please let me know how can I extend my current logic applied in YOY tab  to get the MOM(last month and current month)  and WOW (last week and this week) metrics

             

            Or please let me know how can I achieve the same using your approach , am I doing missing some wrong here . IS this Requirement even achievable ?

             

             

            Thanks,

            anoosha

            • 3. Re: Last month and current month calculation at month level
              David Maning

              Hi,

               

              Really strange... I ran this workbook on other pc and everything was fine. What kind of version do you use? May be you should re-run workbook.

               

              Considering your screen shot, normalized date should be instead of date. It is calculation number one.

               

              However there is also other way to to solve your task.

               

              You can just pick a day and month from date. And then put year on color.

              STR(MONTH([local Dt])) + '.' + STR(DAY([local Dt]))

               

              Please mark this and previous response as helpful.

              • 4. Re: Last month and current month calculation at month level
                anoosha shetty

                hi David Maning,

                 

                I am using 2018.1 version of tableau , and when I try to open from it is throwing the below error

                Screen Shot 2019-02-15 at 3.31.55 PM.png

                 

                Could you please send me a screenshot on how your worksheet looks ? as I cant open your workbook , it would be really helpful for me

                 

                Thanks,

                Anoosha