4 Replies Latest reply on May 3, 2018 7:49 AM by John Cantu

    Dynamic Date Range Based On User Selection

    John Cantu

      Hello Tableau Community,

       

      What I am trying to accomplish seems straight forward, but having a hard time implementing. Basically, I want to provide users the ability to compare a metric (volume) month over month. I created a parameter that allows the user to select either "MoM (Most Recent)", which takes the most recent month in the data set and compares to the previous month, or select "MoM (User Defined)", which they can chose 2 months to compare. I am able to implement the Most Recent option without issues, but it's the User Defined option that I'm having a difficult time with.

       

      I have attached the sample workbook, which is pretty straight forward - 1 dimension MONTH and 1 measure VOLUME.

       

      MoM (Most Recent)

      - Again, pretty straight forward ... created a calculated field using the FIXED LOD to derive the max month in the data set, which allows me to identify the Latest volume, Previous volume and % difference between the two.

       

      MoM (User Defined)

      - I want to accomplish the same thing as above, but this time allow the user to define which 2 months to compare.

      - I know the issue has something to do with the ATTR() function - I'm just not fully understanding how best to take the aggregated month fields, which determine the MIN and MAX months in current view, and filter the volume results.

       

      Sheet 1 illustrates how I am able to identify the max month in the data set, as well as the Min and Max month if the view (you can toggle the months on the right to see how the User defined months change).

      Sheet 2 illustrates how I am able to identify the Latest (based on Max month in data set) and Previous month's volume in order to compare MoM. However, unable to accomplish this with the user defined metrics, which are aggregated dates. Ideally, I'd have this in one metric, but I have it broken out for troubleshooting purposes.

       

      Any help is greatly appreciate. Please let me know if you need clarification on anything.

       

      Thanks,

      John

        • 1. Re: Dynamic Date Range Based On User Selection
          Jim Dehner

          Hi John

          Your workbook is attached

           

          the formulas

           

           

          return this

          they are table calculations (driven by window max or min)

           

          that are calculated DOWN

           

          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: Dynamic Date Range Based On User Selection
            John Cantu

            Hi Jim, appreciate your response. Unfortunately, I won't have the Month dimension in view. The view will basically be a scorecard with the latest volume and the % change compared to the MoM option selected.

            Example: If MoM (Most Recent) selected, then scorecard view would show:

             

            Volume

            18.7M

            ▼ 13.1%

             

            I did try messing around with the partitioning & addressing of the user-defined metrics, but just couldn't figure it out.

             

            Let me know if you have any other thoughts/suggestions.

             

            Thanks again!

            • 3. Re: Dynamic Date Range Based On User Selection
              Jim Dehner

              sorry guess I must have missed that in your previous post

              • 4. Re: Dynamic Date Range Based On User Selection
                John Cantu

                Bump.

                 

                As previous mentioned, one key thing to keep in mind is that the view I am creating is a scorecard that will not have the Month dimension within the view.

                 

                Looks good with the Month dimension in view - able to identify the volume for the minimum and maximum months defined by the user.

                 

                 

                Another screenshot that shows it looks good even if the user toggles the Months:

                 

                However, as soon as I remove the Month dimension from the view/detail, I lose my data. No matter how I mess around with the partitioning and addressing, still no luck.

                 

                Latest Volume (User Defined):

                If Attr([Month]) = Window_Max(Max([Month])) Then Sum(Volume) Else 0 End

                 

                Previous Volume (User Defined):

                If Attr([Month]) = Window_Min(Min(Month)) Then Sum([Volume]) Else 0 End

                 

                 

                Appreciate any feedback y'all have.