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

# Custom Date Level (5 Days)

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)

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)

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)

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)

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.

• ###### 5. Re: Custom Date Level (5 Days)

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)

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)

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.