Changing date field granularity to biweekly

[Marie-LineF]

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

[Max]

Hi @Marie-LineF

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

How to create 15min interval using datetime & value fields in Quicksight

Hi QS community, I am struggling to create 15min interval using datetime & value fields. Basically I am trying to calculate/display number of people count at that given time within the 15min interval based on the value. Calculated field: datetime (15min Interval) - addDateTime((extract(‘MI’, datetime) % 15), ‘HH’, datetime) getting below result |datetime (15min Interval)|value| |2023-02-28 11:00:10|268| |2023-02-28 12:00:04|289| |2023-02-28 13:00:02|304| |2023-02-28 14:00:01|319| |2023…

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

Week Numbers (1-53)

Today the extract() function does not support extracting the ‘week number’ from a date field. As a workaround you can use a formula like this (may need some adjustments +/-1 for certain years, could add some ifElse logic to account for that). FYI this uses weeks starting Sun. dateDiff(truncDate("WK", truncDate('YYYY', {order_date})), {order_date}, 'WK')+1

Let me know if these help.

[Marie-LineF]

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,

[Max]

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')

[Marie-LineF]

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).”

[Max]

Ahh,

I duplicated the number

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

This should work.

[Marie-LineF]

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.

[Max]

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?

[Marie-LineF]

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

[Max]

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^

[Marie-LineF]

Oh that works!! Thanks for the help