1 Reply Latest reply on Mar 28, 2012 3:51 AM by Richard Leeke

    'virtual date frame' using start and end date to blend with other data

    Urs Hugentobler

      Is there a solution to calculate or 'produce' a virtual date frame in Tableau starting from start and end date and generating a series of records (year, month, see below) that can be used for blending with other data (e.g. resource information that may have different values depending on year/month). The goal is to show the data at specific dates (e.g. end of year) but also accumulated over e time period of  a year or over the total 'life cycle'.

      .. or is the only solution to physically produce a data table (in Excel or a DB like mySQL) containing one record for every month?

      thanks, Urs

       

      Bild2.jpg

        • 1. Re: 'virtual date frame' using start and end date to blend with other data
          Richard Leeke

          Tableau won't create virtual rows for you, but you can generate the structure you want very easily from a couple of tables structured like this:

           

          IDStartEnd
          ID1January 2007October 2015
          ID2January 2009December 2020
          ...

           

           

          Month Offset
          0
          1
          2
          3
          4
          5
          ...

           

          You just need a custom SQL connection something like this (except that you will need to replace the red date functions with the correct functions for your datasource, as explained below):

           

          SELECT [DateRanges].[ID],

              DATEADD('month', [Offsets].[Month Offset], [DateRanges].[Start Date])

          FROM [DateRanges], [Offsets]

          WHERE [Offsets].[Month Offset] <= DATEDIFF('month', [DateRanges].[Start Date], [DateRanges].[End Date])

           

          The syntax of the date functions varies a lot for different database back-ends, so you'll need to find the right functions for your database. The easiest way to do that can be to generate a Tableau calculated field which does the date calculation you want just on some dummy data and then look in the Tableau logs to see what SQL that generates for your database.

           

           


          1 of 1 people found this helpful