7 Replies Latest reply on Dec 14, 2016 2:07 PM by Andrew Watson

    Custom Date Level (5 Days)

    Steven Mullin

      Hey guys,

       

      I'm trying to view several months of data with each point being the total of 5 days' worth.

       

      Obviously it is easy to view totals of a day, week, or month, etc. by just selecting the level from the pill; but I can't figure out how I can make my own custom level of 5 days.

       

      I had a look at this Using Custom Date Levels | Tableau Software but it doesn't give me the correct result at all.

       

      Could anybody help?

       

      Example -

       

      DateGrouping
      24 Jan 161
      25 Jan 161
      26 Jan 16

      1

      27 Jan 161
      28 Jan 161
      29 Jan 162
      30 Jan 162
      31 Jan 162
      01 Feb 162
      02 Feb 162
      03 Feb 163
      04 Feb 163
      05 Feb 163
      06 Feb 163
      07 Feb 163
      08 Feb 164
      09 Feb 164
      10 Feb 164
      11 Feb 164
      12 Feb 164
        • 1. Re: Custom Date Level (5 Days)
          Andrew Watson

          It would help if you can show some data and what you expect. For example provide a list of dates and what custom date grouping each date should belong. Providing that information should help get your issue resolved.

          • 2. Re: Custom Date Level (5 Days)
            Steven Mullin

            Hi Andrew,

             

            Hopefully the added table will make my question clearer.  Basically I'm looking to aggregate the days into 5-day intervals, rather than the Weekly default that you get on a Date/Time field.

            • 3. Re: Custom Date Level (5 Days)
              Andrew Watson

              You can convert each date to a number, similar to how a date is stored in Excel, and then divide that by 5. That will give a unique number for each 5 day block, just make sure you ignore the number after the decimal.

               

              To convert the date to a number you can use DATEDIFF, this will ensure your week 1 starts where you want it to. For example for the first week (week 0) to start on 24 Jan 2016 you can do DATEDIFF('day',[DateField],#24 Jan 2016#).

               

              Note I haven't tested any of this but theoretically it should work.

              • 4. Re: Custom Date Level (5 Days)
                Jian Wang

                If you are just working on a few month of data, the quick way could be creating a table in EXCEL with Date, a Date_ID which is incremental number and Date Grouping based on your 5 day grouping logic.

                If you need this for a long term, you may have a table built in database and populated with more data.

                  

                Capture2.PNG

                • 5. Re: Custom Date Level (5 Days)
                  Andrew Watson

                  This is exactly what I was suggesting, although diagrammatically better :-)

                   

                  There's no need to do this in Excel or a database. Your ID is created by the DATEDIFF. You could also do this in Excel, although wouldn't need to use DATEDIFF, in Excel you just minus the Date column from a fixed date (24 Jan 2016 in this example). The fixed date is where you want to begin period 1. In Tableau the grouping formula would be very similar to what you have done in Excel - basically the DATEDIFF result divided by 5 and ignoring the decimal (which you have done by using the INT function in Excel).

                  • 6. Re: Custom Date Level (5 Days)
                    Jian Wang

                    Only read the first sentence of your reply...thought it is converting to yyyymmdd kind of numbers. Yeah, it's ideal if no table is involved.

                    • 7. Re: Custom Date Level (5 Days)
                      Andrew Watson

                      No, converts to a key effectively. The difference in days between 25 Jan and 24 Jan is 1, difference between 26 Jan and 24 Jan is 2, etc. The same as the date_id you created but this is dynamic and doesn't require any 3rd party software, it is all done in Tableau.