DATEADD

It helps to generate the specific date with the specified number interval added to the specified date_part of that date. An example is what we have below;

DATEADD('month', 3, #2004-04-15#) = 2004-07-15 12:00:00 AM

DATEPART

The DATEPART function can be used to return part of a date. The tableau DATEPART function also accepts YEAR, MONTH, DAY.

DATEPART('year', #2004-04-15#) = 2004
DATEPART('month', #2004-04-15#) = 4

DATETRUNC

The Tableau DATETRUNC is used to return the first day of the specified date part. We are to use the first argument to specify the date part which then returns a new date. This DATETRUNC usually accepts YEAR, MONTH, DAY, etc.

DATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM
DATETRUNC('month', #2004-04-15#) = 2004-04-01 12:00:00 AM

How to Calculate Week Numbers by Quarter

We are going to demonstrate this with the Superstore dataset. First, we are to drag our order Date to the Row Pane and select the date as YEAR (this should have been selected automatically). After then, we will also drag the date again to the pane and select it as QUARTER.

Now, we will place in the Order Date again on the Pane but now we will select it as a Week Number

Now, we have our week number on the pane. We can as well do a table calculation to get the Week Number and drop it on the pane by using the DATEPART function. We will use the table calculation below and change the field from continuous to Discrete.

MIN(DATEPART('week', [Order Date]))

After this we need to calculate the weeks by quarter. The DATEPART and DATETRUNC function will be used to calculate this. The formula used is this;

MIN(1+(DATEPART('week',[Order Date])-DATEPART('week',DATETRUNC('quarter',[Order Date]))))

Just like what we did earlier, we will create a calculated field and use the formula above. After this, drag the field to the pane and change the field from continuous to Discrete, which then generates each week’s number by quarter.

Thanks for reading this post. If you have any questions or observations, use the comment box below.