# Changing date field granularity to biweekly

Hello,

Is there a way to change the date field granularity to biweekly? It is not available in the field well, was someone able to achieve this with calculated fields?

Thanks

This allows you to group to the 15 minute mark. It might help you.

You can combine that with the week number to check whether it’s odd or even and then group to a specific week.

Let me know if these help.

1 Like

Hi @Max

I tried a few alternative yesterday, I’m still struggling to get the final results.

When I take the formula from the first example with the 15 minutes interval , I get an error if I modify ‘MI’ to ‘WK’

addDateTime((extract(‘WK’, {date) % 2), ‘WK’, {date})

" Expression extract(‘WK’, {date}) for function extract has incorrect argument type extract(String, Date). Function syntax expects String (Period), Date.

Then I tried to do the logic in 3 steps, but the third formula always gets blocked.

1. WeekStart:
floor(dateDiff(truncDate(‘WK’, {date), {date}) / 7)

To calculate the number of weeks between the truncated start date of the week and the {date} date.

2. BiweeklyOffset:
-(WeekStart % 2)

It calculates the offset in weeks for the biweekly date. It subtracts the modulus of WeekStart by 2 from 0 or -1, depending on the remainder.

3. BiweeklyDate:
addDateTime(truncDate(‘WK’, {date}), -(WeekStart % 2) * 14, ‘DAY’)

To use addDateTime function to add the biweekly offset to the truncated start date of the week. It would multiplies the (WeekStart % 2) by 14 to convert it from weeks to days.

Then I get this error : Expression addDateTime(truncDate(‘WK’, {date}), -(WeekStart % 2) * 14, ‘DAY’) for function addDateTime has incorrect argument type addDateTime(Date, , String). Function syntax expects Numeric, String, Date.

I also tried this formula for #3 :
truncDate(‘WK’, {date}) + ((dateDiff(truncDate(‘WK’, truncDate(‘YYYY’, {date})), {date}, ‘WK’) + 1) * 14)

But I am also getting this error : “Expression + ((dateDiff(truncDate(‘WK’, truncDate(‘YYYY’, {date)), {date}, ‘WK’) + 1) * 14) for function + has incorrect argument type Date + Number. Function syntax expects ‘Number + Number’
‘Date/Time interval + Date/Time interval’
‘Date + Date/Time interval’
‘Date/Time interval + Date’.”

Thanks,

Expression addDateTime(truncDate(‘WK’, {date}), -(WeekStart % 2) * 14, ‘DAY’) for function addDateTime has incorrect argument type addDateTime(Date, , String). Function syntax expects Numeric, String, Date.

This needs to be

``````addDateTime(-(WeekStart % 2) * 14,truncDate('WK', {date}), -(WeekStart % 2) * 14, 'DAY')
``````

Hi @Max

Thanks for the help, I just tried it, I am getting this error:

“Function `addDateTime` should have 3 argument(s) instead of 4 argument(s).”

Ahh,

I duplicated the number

``````addDateTime(-(WeekStart % 2) * 14,truncDate('WK', {date}), 'DAY')
``````

This should work.

I hate to be the bearer of bad news once again haha, here is the error I am getting now

addDateTime(-({WeekStart} % 2) * 14,truncDate(‘WK’, {date}), ‘DAY’) for function addDateTime has incorrect argument type addDateTime(, Date, String). Function syntax expects Numeric, String, Date.

If you take the first part and create a calculated field with it does it work?

`-({WeekStart} % 2) * 14`

Secondly what is the data type it returns?

This formula works, I am getting a string value of 0. When I use it in a pivot table as a column , it multiples my overall total by 2.

I guess it is not really returning the results I need. I don’t know if I can get to a date field grouped biweekly

Here’s what I was thinking with the combining of those two posts.

``````addDateTime(-((dateDiff(truncDate("WK", truncDate('YYYY', {arrival_timestamp})), {arrival_timestamp}, 'WK')+1)%2),'WK',truncDate('WK',{arrival_timestamp}))
``````

Let me know if this works^

1 Like

Oh that works!! Thanks for the help

1 Like