8 Replies Latest reply on Feb 12, 2015 11:06 AM by Divyapriya M

    How do I make a "trailing date range" calculated field?

    daniel chon

      I created a calculated field to create dimensions based on a trailing date range (yesterday, 7 days, 30 days, 60 days, 90 days). Tableau seems to do something weird with the date range dimension field that I created. Below is the calculated field I wrote:

       

      if datediff('day',[day],today()) = 1 then 'Yesterday'

      elseif datediff('day',[day],today()) <= 7 then '7 days'

      elseif datediff('day',[day],today()) <= 30 then '30 days'

      elseif datediff('day',[day],today()) <= 60 then '60 days'

      elseif datediff('day',[day],today()) <= 90 then '90 days'

      else '> 90 days'

      end

       

      Let's say today's date is 6/30/14.

      The "Yesterday" date range should = 6/29/14

      7 days should = 6/24 - 6/30

      30 days should = 6/1 - 6/30

      60 days should = 5/1 - 6/30

      90 days should = 4/1 - 6/30

       

      However, what tableau does is this:

      "Yesterday" = 6/29/14

      7 days = 6/23 - 6/30

      30 days = 5/31 - 6/22

      60 days = 5/1 - 5/30

      90 days = 4/1 - 4/30

       

      Any help?

       

      I attached a screenshot of what my worksheet looks like.

        • 1. Re: How do I make a "trailing date range" calculated field?
          Ben Neville

          You're right, the data gets filtered out by prior conditions - this is because we iterate through our logical statements, and data meets a condition, then the next row is evaluated. You have stumbled onto an important calculation rule in Tableau - data can only exist in 1 place at a time - that is it can only evaluate to a single condition in a statement. If your calculated field exists in this format, Yesterday's data is filtered out of your "7 day" bucket, and the first 7 days are filtered out from "30 days" so that it is only returning 23 days of data, and so on. The only way to do this are:

           

          1. To use a table calculation which takes a running total so that it adds up/down or across the table as needed, so that the 90 day bucket truly contains 90 days of data, or

          2. To create 5 different calculations so that there is no data filtered out by prior conditions

           

          Also, worth noting, simply performing arithmetic on 2 dates always defaults to days, so while I would use a datediff, you could just say:

          "IF today() - [day] = 1 THEN "Yesterday" END"

           

          and create a "Yesterday" bucket. I prefer to take the multiple calculation route, as there is usually a high degree of re-usability in this approach, and you can avoid the issue altogether. You would simply create the calculations and put measure values on text and measure names on your rows or columns shelf as needed. However, from your screenshot I can see you are already using Measure Names/Values elsewhere, so a single approach would be the way to go. As such, use your existing calculation, but click the dropdown next to the measure, select Quick Table Calculation, and use a running total.

          • 2. Re: How do I make a "trailing date range" calculated field?
            Joe Oppelt

            Well, for one thing, your datediff for 7 days should actually be 6.  Today is one of the days in the range.  So Today plus 6 prior days will give you a range of 7 total days.

             

            And you can't just expect 90 or 60 or 30 days to get you exactly to the same day-of-month in a prior month.  Some months have 31, some 28...  But if you want to go back exactly 30 days, just know where it could land in the prior calendar month.  For 30 days, you really want to do datediff of 29.  (Same principle as above.)

             

            Now the reason youe ending date ranges are not cooperating is that your logic is creating buckets that end where the prior bucket starts.  I think you can see that.

             

            Are you looking to get the 90-day bucket to include not only those values that vall between 60-90, but all records in the 60s and in the 30s and in the 7s and in YESTERDAY?  Your ELSE logic is not making that happen.  Please confirm if that's what you really want to happen.

            • 3. Re: How do I make a "trailing date range" calculated field?
              Joe Oppelt

              And if you want the 90 bucket to include the prior ones, Ben explained what to do.

              • 4. Re: How do I make a "trailing date range" calculated field?
                Ben Neville

                I assume they are using a true 30/60/90 day trailing, else the language should change to 1 month/2 months/3 months, but you make a good point, yes. I was assuming he was using yesterday's data, but if there is data from today, you will need to subtract 1 from those values, as today's datediff would = 0, not 1.

                • 5. Re: How do I make a "trailing date range" calculated field?
                  daniel chon

                  that's correct. I want the 90 days to include yesterday, 7 days, 30 days, and 60 days. I'll try out ben's suggestion. thanks.

                  • 6. Re: How do I make a "trailing date range" calculated field?
                    daniel chon

                    Thanks. I'm going to try this later today.

                    • 7. Re: How do I make a "trailing date range" calculated field?
                      daniel chon

                      Using the running total quick table calculation on the measures and compute using "Trailing Date Range" worked perfectly. Thanks!

                      • 8. Re: How do I make a "trailing date range" calculated field?
                        Divyapriya M

                        Hi Ben!

                        Thank you for your inputs. I'm facing exactly the same problem and have decided to work with creating 5 different calculated fields. However, I'm struggling with putting all the 5 into a single view. I need a graph that has "Yesterday, Today, Last 7 days, Last 30 days" on the y-axis and the corresponding values on the x-axis. Below is a screen shot (added two of the calculated fields as a sample)-

                        Capture.JPG

                        Can you please tell me how to make use of the 5 calculated fields and where to place them?

                         

                        Thanks,
                        Divya