COMPLEX WORKDAYS CALCULATION

[rogerpoggi]

Hello everyone,

I oversee a table that captures all the support requests submitted to our team, each demanding a response based on its assigned priority level:

• Urgent: 8 hours
• High: 24 hours
• Average: 72 hours
• Low: 26 days

Within this dataset, we utilize a "Priority" column to indicate the urgency of each ticket and a "Creation_Date" column to document the date, time, and GMT when the ticket was opened. To refine our metrics, I'm seeking assistance in adding two new columns and creating an "Adjusted_Date" column. This adjustment takes into account the following parameters:

• Working hours from Monday to Friday extend from 8 am to 5 pm.
• On Saturdays, support is available from 8 am to 12 pm.
• Sundays and holidays are considered non-working days.

PS¹: The time format is based on a 24-hour duration.

The column "End_Date" represents the date on which the request was closed.

Final objective: this calculated metric serves as a crucial measure to evaluate the timeliness of our team's responses to reported issues. Your collaboration in implementing these adjustments will significantly contribute to our ability to monitor and enhance our response times. Thank you.

See the link to the files (I uploaded them to a public folder on Google Drive): https://drive.google.com/drive/folders/141f6ZHZ4qhgi7lCcgecqJo8dTU0aAUXW?usp=sharing

[lbendlin · Accepted solution]

Sundays and holidays are considered non-working days.

Where is your table with the list of holidays?

How are you planning to handle tickets that were raised outside of the working hours, and/or were closed outside of working hours?

What granularity do you need?  Hourly? Half Hourly?  By the minute?

Working Hours =
VAR a =
    GENERATESERIES (
        [dataDeCriacao] * 1440,
        COALESCE ( [dataDePronto], TODAY () ) * 1440 - 1
    )
VAR b =
    ADDCOLUMNS (
        CALENDAR ( [dataDeCriacao], COALESCE ( [dataDePronto], TODAY () ) ),
        "Start", IF ( WEEKDAY ( [Date], 2 ) < 7, 480 ),
        "End",
            IF ( WEEKDAY ( [Date], 2 ) < 6, 1060, IF ( WEEKDAY ( [Date], 2 ) = 6, 720 ) )
    )
VAR c =
    SELECTCOLUMNS (
        GENERATE (
            b,
            GENERATESERIES ( [Date] * 1440 + [Start], [Date] * 1440 + [End] - 1 )
        ),
        "Value", [Value]
    )
RETURN
    DIVIDE ( COUNTROWS ( INTERSECT ( a, c ) ), 60, 0 )

[rogerpoggi · Accepted solution]

It's working perfectly, but I just realized that I forgot one important thing! 😥

I've left out the last table sent (beginners' mistake, I'm sorry!), the 'kanbanStatus' column which keeps track of the ticket's status:

• Pendente (Pending)
• Em andamento (In progress)
• Aguardando aprovação de orçamento (Awaiting budget approval)
• Feito (Done)

This information is crucial for the ticket study:

When the ticket is 'pending' or 'in progress,' the clock is ticking normally. However, when the ticket is set to 'Awaiting budget approval,' it is put on hold, and so the time should stop. 'Done' is when the ticket is solved.

You can view the RAW database through the following link: https://drive.google.com/file/d/1jA4DG9ZS0hp_5sCnj-sCD4HxhTAP9qpC/view?usp=sharing

[rogerpoggi]

1) Where is your table with the list of holidays? 

- Please see the link to the list of holidays (I uploaded them to a public folder on Google Drive): https://docs.google.com/spreadsheets/d/1IEhgI--mDPwfHgd7Gv1ae_YWUq88VTPcsGCTHfgFC-A/edit?usp=sharing

2) How are you planning to handle tickets that were raised outside of the working hours, and/or were closed outside of working hours?

Tickets raised and closed outside working hours must be realocated to the next workday/hour. 

3) What granularity do you need?  Hourly? Half Hourly?  By the minute?

By the minute.

Thanks!

[lbendlin]

Tickets raised and closed outside working hours must be realocated to the next workday/hour. 

I don't know what that means. Can you please explain?

[rogerpoggi]

The guiding principle of my logic is that if a person is working outside of regular working hours, that is their individual circumstance. It would be unrealistic to expect the same from other workers.

For instance, if a person opens a ticket at 5:35 pm on a Friday, it should be treated as if they opened it at 8 am on Saturday. The same logic applies to closed tickets. If a person closes a ticket at 8:45 pm on a Monday, it should be considered as closed at 8 am on Tuesday.

[lbendlin]

ok - my proposal should cover that.

[rogerpoggi · Accepted solution]

It's working perfectly, but I just realized that I forgot one important thing! 😥

I've left out the last table sent (beginners' mistake, I'm sorry!), the 'kanbanStatus' column which keeps track of the ticket's status:

• Pendente (Pending)
• Em andamento (In progress)
• Aguardando aprovação de orçamento (Awaiting budget approval)
• Feito (Done)

This information is crucial for the ticket study:

When the ticket is 'pending' or 'in progress,' the clock is ticking normally. However, when the ticket is set to 'Awaiting budget approval,' it is put on hold, and so the time should stop. 'Done' is when the ticket is solved.

You can view the RAW database through the following link: https://drive.google.com/file/d/1jA4DG9ZS0hp_5sCnj-sCD4HxhTAP9qpC/view?usp=sharing

[lbendlin · Accepted solution]

You did include that column. Easy enough to add a filter yourself.

[lbendlin · Accepted solution]

Sundays and holidays are considered non-working days.

Where is your table with the list of holidays?

How are you planning to handle tickets that were raised outside of the working hours, and/or were closed outside of working hours?

What granularity do you need?  Hourly? Half Hourly?  By the minute?

Working Hours =
VAR a =
    GENERATESERIES (
        [dataDeCriacao] * 1440,
        COALESCE ( [dataDePronto], TODAY () ) * 1440 - 1
    )
VAR b =
    ADDCOLUMNS (
        CALENDAR ( [dataDeCriacao], COALESCE ( [dataDePronto], TODAY () ) ),
        "Start", IF ( WEEKDAY ( [Date], 2 ) < 7, 480 ),
        "End",
            IF ( WEEKDAY ( [Date], 2 ) < 6, 1060, IF ( WEEKDAY ( [Date], 2 ) = 6, 720 ) )
    )
VAR c =
    SELECTCOLUMNS (
        GENERATE (
            b,
            GENERATESERIES ( [Date] * 1440 + [Start], [Date] * 1440 + [End] - 1 )
        ),
        "Value", [Value]
    )
RETURN
    DIVIDE ( COUNTROWS ( INTERSECT ( a, c ) ), 60, 0 )