4 Replies Latest reply on Sep 1, 2016 12:27 PM by Andrew Watson

    Dynamic date comparison for the same measure?

    Lance Dacey

      I have some tables right now which use a table calculation to calculate the difference in a performance metric month/month or quarter over quarter. Someone requested to be able to have the time period be more dynamic.

       

      Last 5 WeeksLast 5 WeeksLast 90 DaysLast 90 DaysDelta

       

      For example, if a user wanted to select a time period of 5 weeks (X number of days) to be compared to a time period of 3 months (90 days) to see if things have improved for a certain measure. Is there a way to create calculated fields which would aggregate the data for a dynamic date range which could then be altered by a parameter?

       

      For a related topic, is there an easy view built in to show? Ideally, I want the dynamic solution above (because different people always want a different view), but as a temporary fix it would be nice to have the following information on the same table:

      • Last 90 days
      • Last 30 days
      • Last 7 days
        • 1. Re: Dynamic date comparison for the same measure?
          Andrew Watson
          • Last 90 days: IF [DateField]  >= DATEADD('day',-90,TODAY()) THEN [Measure] END
          • Last 30 days: IF [DateField]  >= DATEADD('day',-30,TODAY()) THEN [Measure] END
          • Last 7 days: IF [DateField]  >= DATEADD('day',-7,TODAY()) THEN [Measure] END

           

          This can be parameterised. Create a parameter where the value passed out of the parameter is a number - i.e. the last 30 days would be stored as the value 30 but could be displayed as 30 days. Then just alter the above formula to: IF [DateField]  >= DATEADD('day',-[ParameterName],TODAY()) THEN [Measure] END

          • 2. Re: Dynamic date comparison for the same measure?
            Lance Dacey

            Hi. I had something similar set up but I was running into an issue where I couldn't make proper comparisons. For example, I had the last 7 days and it showed the correct values. When I showed the last 90 days on the same graph it was only showing days 6 - 90. It didn't contain the 5 days. I imagine that this is because I was using it as a dimension so it is filtering the data.

             

            I'd like to have one table with each period separated by a dimension so they can all be visible at the same time and I can throw in some table calculations (compute the delta). My current solution was to make another parameter and another calculation. These seems like it might get cumbersome or inefficient though if there are multiple metrics.

             

            Since the measure I am using is an aggregated number, do I have to create a separate formula for each measure (numerator and denominator)? Most of my metrics are aggregates like (SUM(Field1) + SUM(Field2)) / (SUM(Field1) - SUM(Field3)). Ideally, I would like to be able to have the user select a metric with a parameter drop down and then be able to compare it based on the time periods.

            • 3. Re: Dynamic date comparison for the same measure?
              Luciano Vasconcelos

              There is no magic.

              You will have to build all the calculations.

               

              For having this in dimensions, you would need one line for each time period. This is an ETL problem, not a Tableau issue.

              You can build two parameters and build two calculations based on them and use these calculations.

              • 4. Re: Dynamic date comparison for the same measure?
                Andrew Watson

                Lance, this technique will allow you to do what you want, you'll be able to create the table with the measures and compute the delta. Each calculation will have to be separate. I expect the issue you were seeing before:

                 

                "I had the last 7 days and it showed the correct values. When I showed the last 90 days on the same graph it was only showing days 6 - 90"

                 

                was probably caused by using an IF statement or perhaps a filter that shouldn't have been there.