Preventing duplicated data in summaries

[raynac]

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!

[Clever_Anjos]

you should sum your aggr

=SUM(aggr(sum([Rooms]), building_code, [Date]))

[raynac]

Hi there,

Thanks so much for your message!  Unfortunately, that didn't make any difference to the chart.  Still getting multiple records per date per building.

[Colin-Albert]

Can you post an image of your data model from the Table Viewer, so we can see how you tables are associated?

[raynac]

Hi Colin,

The "ResRevJoin" is a concatenation of the building_code and Stats Date to avoid synthetic keys.  fdpropsumm is the summary with a row per date per building.  And fdresrev is the table that contains a record per reservation per day, and the reservations can only have one guest type each but there can be a myriad of the codes on any given day.

Does this help?