Calculate Boarding Nights For Each Month

Hi everyone,

I have a dataset with the following fields:
Appt ID, Appt Start time, Appt End Time, Location Name
What I am trying to achieve is a Pivot Table with Boarding Nights Count / Month.

I was able to create a calculated field which indicates how many boarding nights were generated every month like this:

Boarding nights/Appt = dateDiff({Appt Start Time}, {Appt End Time})

and then group the result by Location and Appt Start Time(aggregated by month) and I obtained how many nights are there for the appointments starting in each month.

However, I am trying to find a solution to display exactly how many boarding nights are there for each month.
E.g.: Appt starts on 2024-01-28 and ends on 2024-02-03 → I need to see in the table:
Jan 2024 = 4
Feb 2024 = 2

At the moment my table would show only:
Jan 2024 = 6

Thanks in advance!

1 Like

Hello @rusu2k, I have an idea for how to accomplish this. You will need to utilize an ifelse statement to determine the days between the Appt start time and Appt End Time while checking if they ae within the same month. I’ll build out an example calculated field below to try and demonstrate this:

ifelse(
truncDate('MM', {Appt Start time}) = truncDate('MM', {Appt End time}), dateDiff({Appt Start time}, {Appt End time}, 'DD'),
truncDate('MM', {Appt Start time}) < truncDate('MM', {Appt End time}), dateDiff({Appt Start time}, addDateTime(-extract('DD', {Appt End time}), 'DD', {Appt End time}), 'DD'),
dateDiff(addDateTime(-(extract('DD', {Appt End time}) -1), {Appt End time}, 'DD)
)

This function should at least be able to check for each of the scenarios. The only hiccup I am seeing at this point is then making sure it will count the days with the correct month. This may require you to them also create a calculated field for the Months or pull in another date value from your dataset. Let’s start with this and try to work out the next steps.

1 Like

Hello @rusu2k, did my previous response help guide you towards your expected output? I will mark it as the solution, but please let me know if you have any follow-up questions. Thank you!