I have a date column. For example, the date is 8/2/2022, I'm hoping to see that this will convert to Aug Week 1; and if let's say the date is 7/15/2022, then the result should say July Week 3.
Please help, thanks!
1 Answers
You need to use LOD to do so, as default, you can only do week numbers by year in tableau, you need to create two calculated field to do so:
1.Create a [Min-Week] calculation field as shown below:
{ FIXED MONTH([Date]):MIN(DATEPART('week',[Date]))}
Note the calculation above for [Min-Week] assumes that there is at least one record for the first week of each month. If your data does not allow you to rely on that assumption, you could instead use DATEPART('week', DATETRUNC('month', [Date]))
2.Create a [Week-By-Mth] calculation field as shown below :
CASE DATEPART('week',[Date]) WHEN [Min-Week] THEN "Week 1" WHEN [Min-Week]+1 THEN "Week 2" WHEN [Min-Week]+2 THEN "Week 3" WHEN [Min-Week]+3 THEN "Week 4" WHEN [Min-Week]+4 THEN "Week 5" WHEN [Min-Week]+5 THEN "Week 6" END
EDIT good answer - here's just a shorter equivalent expression for [Week-By-Mth]
"Week " + STR(DATEPART('week, [Date]) - [Min-Week] + 1)
If you prefer an integer to a string, you could simplify further by getting rid of the Week prefix and the STR() call.
The [Week-By-Mth] calculation field should be what you need.
Related