How do I Create a Field That Buckets my Dates Into a Fixed Interval?

A Knowi customer needed help grouping payroll data into four week buckets. Grouping by month would not work because the pay period had to start and end on a Friday and be exactly 28 days to match the companies payroll configuration. They also requested that pay period 1 begin on 12/31/21 but the dataset would contain records going back to the beginning of year 2000 (pay periods 0 through -286). How can we write a formula to assign a pay period to each date in the dataset?

This formula can be performed in one query step and only requires one date field and 3 variables we can compute: round((DAYS_DELTA(<date>, X)-Y)/Z,0)

Lets break the formula down step by step:

Variable X is the day before the day your data begins on. In our case we have data going back to 1/1/2000 so we will input 12/31/1999. This will turn each of our dates into a numeric format and organize them into a number line. 

Now that our dates are numeric we can do some simple subtraction to set the 0 on a specific date. We are going to aim for the half way point of the 0 interval and call that variable Y. If we refer back to our original problem the first interval begins on 12/31/21 which is day 8036, if our interval is 28 days we are looking for in between 12/16/21 and 12/17/21 which would give us 821.5 to plug into our equation. If you want the first day of pay period 1 to be your start date you still must compute Y. The formula is Y = (Z+1)/2

Variable Z is the interval of the pay period in days, in our case it is 28. This is used to determine the amount of buckets we have and assign each date to a bucket.

Now to finish up we just need a Round function to round everything to a whole number.

Here are our final results: https://www.knowi.com/w/iiQ7DbPBTiih63WGd1FYH9GU8b9iisw5IFYGCUvyMd5CJcie

And to check our work we can see that each pay period has exactly 28 days: https://www.knowi.com/w/S27gWWQDQhP5L1Kt32wpuJvdAGbgjodVX5MXEo2BzBgie

Now we can apply this to our payroll data and aggregate the number of hours payable grouped by employee and pay period.