
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count number of days for available rooms
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 ?
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
;
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Do you have any idea how to replicate it in QlikSense?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 !

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello William,
I'm not sure app is correctly attached.
attaching again.
Regards,
Raja Indianprakaash

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- « Previous Replies
-
- 1
- 2
- Next Replies »