25 Apr How to Calculate Week Numbers For Each Quarter in Tableau
In this tutorial, we will demonstrate how we can generate week numbers for different quarters of the year in Tableau. Before we move to that, we need to understand some important date functions we will use in our calculation and their uses. With the help of the date function, we can manipulate dates in our data source.
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.
No Comments