Week number for first week of Jan not showing in Tableau

Question

I have the following data structure:

Then I have used the following Tableau function to convert week number and year to first day of the week: DATE(DATETRUNC('week',DATEPARSE("w-yyyy",STR([Week Number])+"-"+ STR([year_axis]))))

Issue, is that when it gets to week 1 and year 2021, instead of showing the week 04/01/2021, it aggregates this data to the last week of December (28/12/2020).

Anyone knows why?

Thanks!

AnilGoyal AnilGoyal · Accepted Answer · 2020-11-27T12:07:26

Yes, depending upon your locale, tableau starts a week either on Sunday or a Monday. Moreover, if a first day of a year is not Monday, then the dates before first Monday are considered as week 0 of that year.

Use of this calculation is suggested in this case (for newer versions of tableau desktop)

DATE(DATETRUNC('week',
IF ISOWEEKDAY(DATEPARSE("w-yyyy",STR([Week Number])+"-"+ STR([Year Axis]))) =1
then DATEPARSE("w-yyyy",STR([Week Number])+"-"+ STR([Year Axis]))
else DATEPARSE("w-yyyy",STR([Week Number]+1)+"-"+ STR([Year Axis]))
END
))

For those versions of tableau where ISOWEEKDAY is not listed, the following calculation is suggested

DATE(DATETRUNC('week',
IF DATEPART('weekday', DATEPARSE("w-yyyy",STR([Week Number])+"-"+ STR([Year Axis]))) =1
then DATEPARSE("w-yyyy",STR([Week Number])+"-"+ STR([Year Axis]))
else DATEPARSE("w-yyyy",STR([Week Number]+1)+"-"+ STR([Year Axis]))
END
))

If first day of Year is Monday then your calculated field otherwise week+1.

See the screenshot where both calculations are compared. (Note for year 2018)

I hope this solves your problem.

Week number for first week of Jan not showing in Tableau

1 Answers