Occupation rate by day

[Alberto_Cova]

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

[Aurelien]

Hi @Alberto_Cova

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

help.appsheet.com

TOTALMINUTES()

Count of minutes in Duration

help.appsheet.com

Math Expressions

Calculate numeric values from other numeric values.

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

help.appsheet.com

SELECT()

Gather column values from row in table or slice

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Core concepts & functions Expressions: The Essentials FILTER() List Expressions and Aggregates LOOKUP() MAXROW() MINROW() REF_ROWS() SELECT() What is a Key? Adding & updating rows Is this a new row?/Does this row already exist? What is the previous value of this column? Excel alternatives How do I do COUNTIF() or COUNTIFS()? How do I do SUMIF() or SUMIFS()? How do I do VLOOKUP()? Last row of the spreadsheet How do I get the last row of the spreadsheet? How do I get a column value from…

[Alberto_Cova]

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.

[Austin]

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.

[Alberto_Cova]

Austin_Lambeth:

C

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

[Nicolas_Feldman]

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.

[Austin]

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

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.

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.

[Nicolas_Feldman]

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

About this:

Austin_Lambeth:

taking that date table and adding an occupancy number that you update whenever someone makes a reservation

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.

Unless… Actions?
I believe Im about to learn something here…

Thanks again Austin!

[Steve]

Austin_Lambeth:

know that counting/summing is one of the hardest things for Appsheet performance.

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

[Austin]

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.