3 Replies Latest reply on Jun 20, 2018 10:09 AM by Alex Braun

# Dynamic Week numbers based on a flag

Hi All,

I have a requirement to show where if a flag is 'Yes' for 3/4/2018 and onward dates, I need to flag the week of 3/4 as Week 1, next as Week 2 and so on.

This flag will keep changing over time to have different dates flagged. So, the solution needs to be dynamic where the Week 1 changes based on this flag.

I have tried taking out the week start date and end date, but it shows 'Week 1' for all subsequent weeks as they are also flagged as 'Yes'.

I am stuck on this for a while now and any help would be greatly appreciated.

Thanks!

Vedant

• ###### 1. Re: Dynamic Week numbers based on a flag

Could you do a datediff function? dateadd('week',-datediff('week',datetrunc('year',[date]),[date])+1,[date])

So this equation would get the number of weeks between the first of the year and the target date, then subtract the number of weeks and add 1 to account for week 1 to the date to adjust it back to january and then anything else should reference whatever date you choose.

• ###### 2. Re: Dynamic Week numbers based on a flag

Hi Alex,

I tried this, but it is giving the result as 1/7/2018 and it's week. Maybe I missed something.

Could you please explain, how I can get 'Week 1' as text for week of 3/4/2018 and 'Week 2' as text for week of 3/11/2018 and so on.

Thanks!

Vedant

• ###### 3. Re: Dynamic Week numbers based on a flag

It appears that this one is a unique case, the first sunday of the month is the 7th, so technically the way tableau would calculate it as the first full week would be week 53 of 2017 instead of week 1 of 2018. You could also do a calculated field like this

'Week ' + Str(DATEDIFF('week',[Date],[Date]+7))