Group dates into 2 week blocks

[andnichols]

Hi, 

I am wondering how I can take a plain date field and set it up in a table or filter so that the dates are filtered into Pay Periods (2-week groupings)? Appreciate any help!

[Andrei_Cusnir]

Hello,

I am not 100% sure that I understood the use case scenario that you are trying to achieve. However, here are some steps that might get you on the right path:

1. I have loaded the dataset:

2. Now we can use a combination of the following expressions:

• WeekStart(Date) // This will give us the start date of the week
• WeekEnd(MakeDate(Year(Date), Num#(Date(Date, 'M')), 15)), 'Week A', 'Week B') // This will get the date's year, month number and will create a date that represents the middile of the month
• Combining together in If statement, we have: =If(WeekStart(Date)<=WeekEnd(MakeDate(Year(Date), Num#(Date(Date, 'M')), 15)), 'Week A', 'Week B')// It checks if the beginning of the week falls bellow the middle of the month, then it is in category A, otherwise, it is in category B.

Outcome is:

As you can see we now see the dates split in groups of 2. This is more visible in Pivot Table:

You can also filter based on Month for example and modify the expressions of the configuration of the visualizations based on your needs:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[MarcoWedel · Accepted solution]

one solution could be as well:

table1:
LOAD *,
     WeekDay(Date) as WeekDay,
     WeekName(Date) as WeekName,
     Dual(Date(Floor(Date,14,2))&' - '&Date(Floor(Date,14,2)+13),Floor(Date,14,2)) as PayPeriod;
LOAD Date(Today()-RecNo()+1) as Date
AutoGenerate 50;

hope this helps

Marco