Calculate values based on unique dates

1

This is probably very simple but I am stuck. Input: four columns. The first is ID no. Irrelevant to this problem. Second is date, third - Number of rooms. Fourth - room type. Notice that Room values are the same for same date, Value is the same.

Output needed: sum of rooms for unique dates (eliminate duplicates) for all rooms and then by room type.

I tried to use unique() formula on Reservation Date but could not figure out how to tie it with the Room number.

Thank you!

2

Is this what you were looking for ? :blush:
(I’ve tested those on your doc, in the table)

Count Sum of Rooms for unique Reservation dates only. The answer should be 2+3+4+1=10.

27
271106Ă—170 14.8 KB

Count Sum of Room for unique Reservation dates by type of Room. The answer should be for Small rooms: 4, for Big rooms: 6.

23
231106Ă—214 22.9 KB

36
361106Ă—214 22.9 KB

3

Hi @Pch. Unfortunately these equations do not work. They operate “Unique” formula on Room count not on Dates. It should be Room values for Unique dates. I have a suspicion that Unique formula does not work on dates here.

I have updated the Sample table to reflect that. @Krunal_Sheth - maybe you could help here? I am confused to what Unique formula can be of use. If I use it like “[Table 1].RezervationDate.Unique()” then it returns me unique values, but if I try to use it in filter “Filter([Table 1],RezervationDate).Unique()” then it does not filter unique rows. I guess it is because It cannot decide which ones to select from many.

4

Hmmm :thinking: .

I must say I don’t understand the problem with Unique() here either :thinking:
(which is bothering me a lot :yum: )

And there’s another thing I don’t understand concerning the formulas :

  • [Table 1].RezervationDate.Unique()

  • Filter([Table 1],RezervationDate).Unique()

Why in both the results the very first date is formatted as YYYY/MM/DD but the remaining results are formatted as MM/D/YYYY ? :thinking:
50
This makes no sense :thinking:

I’ve taken another look at your doc anyway (because it got me thinking :yum:) and came up with something but I’m not sure if this is correct :sweat_smile: .

I’ve reproduced your table and added a field Name because working on this, I couldn’t differentiate the rows using solely the dates.

I think I’m on the right path, but profoundly unsure :thinking:

First, I’ve filtered by dates creating a field Filter Date with the formula :
(Getting all the rows for the date)

Filter([Unique dates],RezervationDate=thisRow.RezervationDate)

Then I’ve filtered that field by ID (in the field Filter Date ID)
(Creating some kind of “cumulative sum”)

Filter(thisRow.[Filter Date],ID<=thisRow.ID).Unique()

And then, in the field Date ID = Date, I’ve compared both of those fields to extract the unique values for the dates, getting rid of the duplicates (not sure I can say this differently :sweat_smile: ) with the formula :

Filter(thisRow.[Filter Date],thisRow.[Filter Date ID]=thisRow.[Filter Date])

And finally, I get to the Rooms and the Sum() (The formulas on the canva are just below the table :slight_smile: )

5

Chiming in.

First of all, I think the fact that you can have multiple entries with the same date and number of rooms is the problem itself. And the best way to approach your need would be to get rid of duplicates in the table in the first place.

But let’s assume that for some reason it’s not possible. Logically I’d approach the problem in the same spirit:

  1. Determine if a row is a duplicate of already present row — I’d make a separate column for that
  2. Then calculate only rows that aren’t duplicates

Much simpler than @Pch’s approach :slight_smile:

In tasks like this, it’s always best to figure out what is causing complications to an otherwise trivial task, then find a way to eliminate them in the first step.

6

True :yum: :rofl: !

I think I was too much « in it » , couldn’t distance myself and once more, lost sight of « simple things » I may have tried :slightly_smiling_face:.

Still learning everyday being here :grin: : Thanks @Paul_Danyliuk :grin:!

7

Dear @Pch and @Paul_Danyliuk. Your remarks got me thinking and I have realized that I probably constructed table with incorrect logic. What I tried to do in the column “Rooms” was to keep a tab of how many rooms are booked in total for that day (I have corrected it now to represent it with the formula and introduction of “hour slots” column), thinking that in such way I could tally up the numbers easier. But as @Paul_Danyliuk pointed out, - it is a wrong approach to start with and required deduplicating rows that would then miss other important data. So I will try to think of another logic to count number of rooms slots reserved for every day now using count of hour slots for every day. Probably much easier approach :wink: Thanks a million to both for helping out.

@Paul_Danyliuk and @Pch - if you could have a look at Calculate occupancy rates for a business when dates with zero activity are not in the data table and help me with some sort of similar insights, I would truly appreciate that.

Kind regards. Vytenis

1 Like