Using Custom Date Levels(in dimension)

Question

I have a date column at the date level. I would like to use the date level at the granularity of two weeks (biweekly). Could you please help me in this regard.

Answer 1

• Calculation can be created to aggregate the dates at a biweekly level.

• At this point of time in this case, I am using a date field called [Date]:

IF DATEDIFF('week', DATETRUNC('week', [Date]), #01/01/2015#, 'monday') % 2 = 0 THEN

    DATEADD('week', 2, DATETRUNC('week', [Date])) -1

ELSE

    DATEADD('week', 1, DATETRUNC('week', [Date])) -1

END

• This calculation performed above checks if a date is an even or odd number of weeks from the reference date (in this case #01/01/2015#). I have also defined weeks as starting on Mondays.

• The difference between date1 and date2 expressed in units of date_part is returned by DATEDIFF(date_part, date1, date2, start_of_week). The start_of_week parameter is optional. The start of week is determined by the data source, if it is omitted.

• Refer to this link for more details:

http://onlinehelp.tableau.com/current/pro/online/mac/en-us/functions_functions_date.html