5 Replies Latest reply on Jun 12, 2012 10:23 AM by kathryn.chrismer

# Last Week/Previous Week in a New Year

I have a simple calculation to determine Current Week, Last Week and Previous Week based on TODAY. In the new year it no longer works properly due to the week number being less than one. Any suggestions on how to adjust the calculation to account for the new year? (Current Week = Week 1, Last week = Week 52, Previous Week = Week 51)

CASE DATEPART('week', [BeginDate])

WHEN DATEPART('week', TODAY())  THEN 'Current Week'

WHEN DATEPART('week', TODAY()) - 1 THEN 'Last Week'

WHEN DATEPART('week', TODAY()) - 2 THEN 'Previous Week'

ELSE 'Older' END

• ###### 1. Re: Last Week/Previous Week in a New Year

Yep, you will want to use datetrunc instead so that you preserve the full date. Something like

if datetrunc('week',today())=datetrunc('week',[BeginDate]) then 'This week'

elseif datetrunc('week',today())=datetrunc('week',[BeginDate])+7 then 'Last Week'

elseif datetrunc('week',today())=datetrunc('week',[BeginDate])+14 then 'Previous Week'

else 'Older' end

I tested this using a data set with years instead and it worked. Please test on your data.

• ###### 2. Re: Last Week/Previous Week in a New Year

Alex did you do the 'rating' on this post? How does the rating system work?

--Shawn

• ###### 3. Re: Last Week/Previous Week in a New Year

I did - I thought it was an interesting question (and wanted to see what the stars meant...)

• ###### 4. Re: Last Week/Previous Week in a New Year

This is really helpful, but I have a quick question.

I want to compare week over week, but how would I edit the formula so that they would measure up to the same day of the current week? For example, if current week activity is through Wednesday, I'd want the previous week data to also show data only through Wednesday. Is that possible to do?

Thanks!