Occupation rate by day

Can someome please help me ? trying to create a report on that show me the occupation rate by day.
Have a table that contain [IN] (date) and [OUT] date for the customers to fill in.
However we do have limited rooms availible so I need to create a report that show me by day the occupation.
Can this be done in appsheet or should I try to do this in google sheet and link it to the tables in appsheet

Hi @Alberto_Cova

Yes it can be done with AppSheet.
Please have a look here (3rd and 5th example)

And additional resources for your need, because you will probably want to build a select expression to make your calculations per day:

3 Likes

Hi Thanks for the reply .
Or I can not find the answer as mention or I did not explain my question correctly.
Example
Client 1 from 1 jan till 2 Jan
Client 2 from 5 jan till 6 Jan
Client 3 from 4 jan till 7 Jan

The report should display

Date Count availible rooms (17-Count)
1 Jan 1 16
2 Jan 1 16
3 Jan 0 17
4 Jan 1 16
5 Jan 2 15
6 Jan 2 15
7 Jan 1 16

ect.

Youā€™ll need a sheet that has the list of days, then COUNT(SELECT(Reservations[any column], AND([IN]>[thisrowdate], [OUT]<[thisrowdate])). This would tell you how many reservation there are for a date.

that formula will need cleaning but it should be what you need in a view and then you can schedule a bot to email you the contents of that table at your need.

2 Likes

OK Thanks I believe this make sense will try to work this out later today

1 Like

Im with @Alberto_Cova here, trying to get an actual occupation rate per day.

As @Austin_Lambeth noted, im trying this with a table with a list of days and a VC that counts the bookings or ā€œrooms occupiedā€ for each day.

  1. Because not all my bookings count the same for the Occupation rate (some bookings have more than one room), I first calculated the number of rooms occupied for each reservation with on a VC called
    [OcupaciĆ³n diaria] in the Reservations table.

  2. In another table with a list of dates, I added a VC called [OcupaciĆ³n] that would SUM all Reservas[OcupaciĆ³n diaria] for each date with this:
    sum(SELECT(
    Reservas[OcupaciĆ³n diaria],
    AND([_THISROW].[Fecha]>=[Check in],
    [_THISROW].[Fecha]<[Check out],
    [Status Reserva]=ā€œBookedā€) ) )

The problem im facing is that this formula its so time consuming, its not realistic to have it in the app. Almost two and a half minutes to calculateā€¦

Any other ideas to simplify this would be greatly appreciated.

1 Like

If you use references and dereferencing it should be faster but know that counting/summing is one of the hardest things for Appsheet performance.

Consider possibly taking that date table and adding an occupancy number that you update whenever someone makes a reservation so then your sum is across a real column instead of being a sum across a sum virtual column. This could cause issues if you have say separate locations(different hotels, different complexes, different management group, etc) that have different occupancies.

4 Likes

Thank you @Austin_Lambeth for your insight on this. I didnĀ“t know COUNT/SUM was so consuming for Appsheet.

About this:

I think IĀ“ll give it a try, although Im not sure how to achieve that ā€œupdateā€ in the ā€œDates Tableā€ whenever theres a new booking in the ā€œReservations tableā€.
Im using VC because I thought that was the way to autoupdate values real time.
:thinking:

Unlessā€¦ Actions?
I believe Im about to learn something hereā€¦

Thanks again Austin!

To clarify, the SELECT() used when counting or summing is the problem, not the counting or summing specifically. Using dereferences, when available and appropriate, are a substantial benefit over SELECT().

2 Likes

Hm it always felt like the COUNT() or SUM() was the issue but I guess whenever I used COUNT() I often had a decently complex SELECT inside or a select inside of a select which should have already been a red flag to not do.

Actions or Bots would be the way to achieve that. I would give the referencing/dereferencing method a try first since it would be WAYYYYYYY simpler and could easily give enough of a benefit to make the other method not worth it. Also remember that your data set is gonna grow over time so take advantage of security filters to keep the number of rows down. If you need to look at old reservations recommend doing that in a separate app.

2 Likes