# 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
New Contributor

## 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 ?

12 Replies
Highlighted
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() .

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

## 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.

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

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

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

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

## Re: Count number of days for available rooms

Hello William,

I'm not sure app is correctly attached.

attaching again.

Regards,

Raja Indianprakaash

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