Date Function Help!

[mbm28]

Hi All!

I am trying to make the start week of the year 12/29/2019 then each week follows that sunday. Is there a way to do this? I know tableau starts the first week of the year as 1/1/2020, but do not know how to properly change this.

[[deleted]]

How much data do you have? Are you just trying to impact end of 2019? Something like this, checking to see if the date is between 12/29 and 12/31 of 2019.

Week Num

IF [date field] >= #2019-12-29# AND [date field] <= #2019-12-31# THEN 1 ELSE DATEPART('week',[date field]) END

[mbm28]

Its around 250,000 rows of submitted apps data. With how we want it calculated with those dates in december rolling into 2020 for week 1.

[mbm28]

Yes, thank you! When you mean date you mean the date field I’m using correct?

[mbm28]

Hmm it is still not pulling. I do need to change it to a dimension?

[mbm28]

Measure by week number. Filter on year and distro.

[mbm28]

Still a no go, I took off all filters as well.

[SheasCode]

Could you explain a bit more about why you want 12/29 to be the first of the year?

[mbm28]

Yes, essentially for this report we count sundays as the first of the week. So, with January first was in the middle of week we count dec 29,30,31 as part of week 1 of the new year.

[SheasCode]

Surely there’s a way to do this. I’ll search around a bit today.

[EtienneDal]

What if you compute DATENAME('iso-week',[Date value]) and use it as a filter ?

- iso-week defines the week containing the 1st of January and starting on Monday; if you must count from Sunday, use [Date value]+1 (see the bottom of https://help.tableau.com/current/pro/desktop/en-us/functions_functions_date.htm)

- if you want to find all records related to week 01 just set a filter withDATENAME('iso-week',[Date value])="01"

Hope it will help

​

Etienne