Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Highlighted
williamyuen96
New Contributor

Count number of days for available rooms

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

as.PNG

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 ?

12 Replies
Highlighted
Partner
Partner

Re: Count number of days for available rooms

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

image.png

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
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
williamyuen96
New Contributor

Re: Count number of days for available rooms

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
];

 

Highlighted
Partner
Partner

Re: Count number of days for available rooms

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. 

 

image.png

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;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
williamyuen96
New Contributor

Re: Count number of days for available rooms

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?
Highlighted
Partner
Partner

Re: Count number of days for available rooms

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

image.png

 

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 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
williamyuen96
New Contributor

Re: Count number of days for available rooms

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 !


Highlighted
Partner
Partner

Re: Count number of days for available rooms

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

Highlighted
Partner
Partner

Re: Count number of days for available rooms

Hello William,

I'm not sure app is correctly attached.

attaching again.

Regards,

Raja Indianprakaash

Highlighted
williamyuen96
New Contributor

Re: Count number of days for available rooms

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?