12 Replies Latest reply on Dec 13, 2018 12:15 AM by Chris McClellan

# Group dates into 4 week periods

I am trying to group dates into 4 week periods, I have found this post by Mark Bi-weekly data grouping  and it works perfect for two weeks. Can anyone figure out how to modify this calculation to group into 4 weeks instead of 2 weeks?

This is Marks original Calculation:

IF DATEDIFF('week',DATETRUNC('week',[Date Field]),#7/27/2014#)%2=0 THEN

ELSE

END

• ###### 1. Re: Group dates into 4 week periods

Hi LL

the formula you posted uses a function called Modulo - it is the expression %2

what it does is divide the expression by 2 and return the remainder - if it is 0 then it is true and executes the then clause

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Group dates into 4 week periods

Hey Jim,

that does not return the desired results, please see example that is attached. That was my first thought also but it is doing something funky when you change that to 4.

• ###### 3. Re: Group dates into 4 week periods

it the formula in your post worked on a 2 week cycle

this will work on 4

IF DATEDIFF('week',DATETRUNC('week',[Date Field]),#7/27/2014#)%4=0 THEN

ELSE

END

it it did not work so I can see what is going on

JIm

1 of 1 people found this helpful
• ###### 4. Re: Group dates into 4 week periods

You have to take into account the  4 possible returned values of the  modulo 4.

Try this

IF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=0 THEN

DATETRUNC('week',[Order Date])

ELSEIF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=-1 THEN

ELSEIF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=-2 THEN

ELSEIF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=-3 THEN

END

Michel

3 of 3 people found this helpful
• ###### 5. Re: Group dates into 4 week periods

This is the correct answer, one more thing how would you modify this so that the max date is always the end of the most recent period. in my data set the date is the week ending date so only one date per week.

so if I have:

Time Period End Date

10/7/2018

9/30/2018

9/23/2018

9/16/2018

9/9/2018

9/2/2018

8/26/2018

8/19/2018

this would be the desired result:

4 week period
Time Period End Date
10/7/2018

10/7/2018

9/30/2018

9/23/2018
9/16/2018
9/9/20189/9/2018
9/2/2018
8/26/2018
8/19/2018
• ###### 6. Re: Group dates into 4 week periods

This should work:

```case DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4
END
```

But Michel Caissie deserves the correct answer on this one, I just rewrote it to be a bit easier to read and changed the dates to match your request.

1 of 1 people found this helpful
• ###### 7. Re: Group dates into 4 week periods

This is much cleaner but doesn't address making the time periods align with the max date in the dataset. you can think of it as a rolling time period, so the max date is the is the most recent period and include the previous 4 weeks and so on.

• ###### 8. Re: Group dates into 4 week periods

OK, I don't understand exactly what you mean - can you create some dummy data in CSV or XLS to explain ?  Even if it's just the time dimension and what you expect .....

OR ... do you mean that the 4 week period allocation will change every day when a new day is added ?

• ###### 9. Re: Group dates into 4 week periods

Ok best way to think of this is that the start of the 4wk time period is the max(date) in the dataset and the other members of that 4wk time period are WEEK(MAX(DATE))-4.

In my dataset the date field is the week ending date so there is only one date per week.

Example:

10/7/2018

9/30/2018

9/23/2018

9/16/2018

9/9/2018

9/2/2018

8/26/2018

8/19/2018

So in this example the first time period would be 10/7/2018 and it would include 10/7/2018, 9/30/2018, 9/23/2018, 9/16/2018 then the 4wk periods go on from there. So the next period would be 9/9/2018 and it would include 9/9/2018, 9/2/2018, 8/26/2018, 8/19/2018 and so on. But it will be rolling so when a new date is added that will be the start to the 4wk periods.

• ###### 10. Re: Group dates into 4 week periods

OK, I think this is what you're after

1 of 1 people found this helpful
• ###### 11. Re: Group dates into 4 week periods

Bingo that work... thank you so much!

• ###### 12. Re: Group dates into 4 week periods

Awesome, happy to help