I am hoping that someone here can assist with this. I have two tables that need to join together based on building code and date. I do that in the script.
One of them has a record of available rooms per day and building; the second has many records (with fields that include building code and date) that I add up to get rooms occupied. One of these summaries is by guest type, so I will use that as my example, but I summarize by other fields as well.
Here is the main problem: if I try and sum() the available rooms, I get a record for each guest type. So if I have 150 rooms but 4 guest types, my chart says I have 600 rooms. I can use max(), but then when I try and do this for a week and need, say 1050 (150 X 7) rooms, the max produces just the 150, which is totally incorrect.
I am convinced there has to be a reasonable way to get 150 rooms per day, period, and have it summarize by week, month etc as needed. I tried AGGR:
=aggr(sum([Rooms]), building_code, [Date])
But that gives me the same issue so either I have chosen the wrong function or I am doing it incorrectly.
Does anyone have any insight they can offer?
Thank you!
