0 Replies Latest reply on Dec 5, 2016 3:05 PM by Matt Coles

    Tip: Relative date filter for previous 15 minutes

    Matt Coles

      Relative date filters are a great trick to use for VizAlerts, because date values in your data can be useful to "anchor" against to prevent duplicate alerts from going out. For example, perhaps you have an event you want to alert for in your data, along with the date-time that it occurred. If you only want a single email for a given event, you can add a relative Date Filter against that date value to your viz, and pick something like "previous hour". Then, if you schedule your alert to run on an hourly VizAlerts schedule, when the event occurs, at the top of the hour your alert will look for an event meeting your criteria that happened only in the previous hour, then alert you about it. When it runs during the next iteration, it won't alert you for the same event, because it occurred earlier than the previous hour.


      You could pick "last 60 minutes" and accomplish nearly the same thing--but I do not advise this, because there is no guarantee that your alert will run at exactly the same time each hour. Other alerts may run first, delaying your alert slightly, and changing the window your alert looks at.


      It can be challenging to build an alert that uses a relative date filter window that you need to run on an every-15-minutes schedule (the fastest schedule Tableau Server will let you create), because there is no out-of-the-box relative date setting that lets you pick "Previous 15-minute window". So here is a calculation you can use to do that:


      [Last Quarter Hour End]

      // Get the timestamp for the end of the previous quarter hour interval (e.g. if it's 8:35am now, return 8:30am)
          (INT(DATEPART('minute', NOW()) / 15)) * 15, 
          DATETRUNC('hour', now()) )


      That gives you the date-time for the end of the last 15-minute window.


      [Last Quarter Hour Start]

      // Get the timestamp for the start of the previous quarter hour
      DATEADD('minute', -15, 
          [Last Quarter Hour End]


      That gives you the date-time for the start of the last 15-minute window.


      Now, just make sure the date-time in your data is between those two date-times, and you'll ensure that your event occurred only in the last 15-minute window!


      [Occurred During Previous Quarter Hour]

      // Did the event occur in the previous quarter hour? 
      // (this calc allows us to run the alert every 15 minutes withou generating duplicates, or missing any alerts)
      ([Completed At] > [Last Quarter Hour Start]
      [Completed At] <= [Last Quarter Hour End])