Count number of days for available rooms

[williamyuen96]

I'm building a dashboard for hotel purpose and I have this data set 



I having a problem here to come up with a formula how to calculate available room on the given date since there are many different people check out on a different date.

I tried Googling it and I found a solution where COUNTIFS is being used in excel. Anybody know how can I solve this in qlik sense measure ?

[Vegar]

You don't present any info about available rooms in your data, but assuming you only have single bed rooms you could at least see how many rooms that are occupied for a certain date by using IntervalMatch() .

For generating the table above I used the following data script.

SET DateFormat='YYYY-MM-DD';
Bookings:
LOAD * 
inline [
  Guest,   Checkin,    Checkout
  Vegar,   2019-07-01, 2019-07-05
  William, 2019-07-02, 2019-07-04
  Lars,    2019-07-04, 2019-07-12
  Rob,     2019-07-07, 2019-07-11
  Lisa,    2019-07-04, 2019-07-05
];

For _date = date('2019-07-01') to date('2019-07-12')
Calendar:
LOAD 
  dayname('$(_date)') as Date
  AutoGenerate 1;
NEXT

IntervalMatch (Date) 
LOAD 
  Checkin, Checkout
RESIDENT 
  Bookings
;

[williamyuen96]

Interesting, but what if the data set is already loaded in my Qlik Sense?  I assumed I shouldn't use something like this ?

LOAD * 
inline [
  Guest,   Checkin,    Checkout
  Vegar,   2019-07-01, 2019-07-05
  William, 2019-07-02, 2019-07-04
  Lars,    2019-07-04, 2019-07-12
  Rob,     2019-07-07, 2019-07-11
  Lisa,    2019-07-04, 2019-07-05
];

[Vegar]

Do you have any other data or only this dataset? In order to get all dates, even those without an check in or check out date you will need a calendar containing all dates. 

I removed the intervalmatch from my earlier script and rewrote the expression using an IF() statement. 

SET DateFormat='YYYY-MM-DD';
Bookings:
LOAD Guest, Checkin, 
Checkout
inline [
  Guest,   Checkin,    Checkout
  Vegar,   2019-07-01, 2019-07-05
  William, 2019-07-02, 2019-07-04
  Lars,    2019-07-04, 2019-07-12
  Rob,     2019-07-07, 2019-07-11
  Lisa,    2019-07-04, 2019-07-05
];

For _date = date('2019-07-01') to date('2019-07-12')
  LOAD dayname('$(_date)') as Date
  AutoGenerate 1;
NEXT

//IntervalMatch (Date) 
//LOAD 
//Checkin, Checkout
//Resident Bookings;

[williamyuen96]

Thanks for the input, i'm originally Power BI expertise and i came up with a solution but having trouble replicating it in qlik sense script.

Occupied rooms= CALCULATE (

COUNT(Customers[Guest]),

FILTER (

ALL (Customers),

MAX (Occupied[Date]) >= [Check In]

&& MAX (Occupied[Date]) < [Check Out]

)

)

Do you have any idea how to replicate it in QlikSense?

[Vegar]

It is pretty much what I did in my earlier example, but you could adjust the expression to this if you prefer count above sum

count(IF( Date>=Checkin AND Date<Checkout, Guest))

I also noticed that you had a bit different logical clause, I used Date=<Checkout .  You used Date<Checkout 

Ive changed that in my new count()  expression 

[williamyuen96]

Successfully done it but 1 problem... I am unable to join the table between my master calendar,Dates (Dates Column) with Customers (Checkin column). If I did, I could not create a measure condition to compare the dates column with the check-in column. 

However, if i do not join the two tables together, the calculation works with using Dates (Dates Column) in the filter pane but do not work for simple calculation like counting the number of guest.

In your experience, how do I solve this ? Ideally I would love them to join together. The dataset and app is below. Thanks !

[raja_indianprak]

Hello William,

Attached APP really satisfy your needs?

I've included Datepicker Extension and it holds dimension called Datess from Excel you shared (Dates).

And checkin and Checkout date properly aligned with auto calender.

Regards,

Raja I

[raja_indianprak]

Hello William,

I'm not sure app is correctly attached.

attaching again.

Regards,

Raja Indianprakaash

[williamyuen96]

No, it's not working. I tried filtering from the date picker and it only filters the room occupied KPI but leave out the number of guest KPI. How do I make the date picker to filter both KPI?