When using different Date Parts in Tableau it can be annoying when using weeks. Tableau displays weeks as the week number based on the entire year (which isn't very helpful for an end user of a dashboard). Thankfully, there's an easy way to make your own weeks that are easier to understand!
The Calculated Field
"W/C " +
if LEN(STR(DAY(DATETRUNC('week',[DATE])))) = 1 then
"0"+STR(DAY(DATETRUNC('week',[DATE])))
ELSE
STR(DAY(DATETRUNC('week',[DATE])))
END
+"/"+
if LEN(STR(MONTH(DATETRUNC('week',[DATE])))) = 1 then
"0"+STR(MONTH(DATETRUNC('week',[DATE])))
ELSE
STR(MONTH(DATETRUNC('week',[DATE])))
END
Explanation
I will break down the above calculation into different chunks so you can fully understand what is going on.
Part 1
"W/C " +
if LEN(STR(DAY(DATETRUNC('week',[DATE])))) = 1 then
"0"+STR(DAY(DATETRUNC('week',[DATE])))
-- this essentially checks if that day of the month is a single digit number, and if it is, add a '0' before it for consistency.
ELSE
STR(DAY(DATETRUNC('week',[DATE])))
END
-- If the above condition is not met (the number is a two digit number) then just return the day of the month
Part 2
+"/"+
-- To give the '/' between the week starting date and month
if LEN(STR(MONTH(DATETRUNC('week',[DATE])))) = 1 then
"0"+STR(MONTH(DATETRUNC('week',[DATE])))
-- The same logic as before, if the month is a single digit character then add a '0' before it
ELSE
STR(MONTH(DATETRUNC('week',[DATE])))
END
--Otherwise, just return the month
If you have any questions don't hesitate to message me on Linkedin. Thank you for reading and I hope this helped!