How to calculate the Date field in between InDate and OutDate

[sunilkumar6]

Hi,

 I have below guest house highlighted field reg no,  Room no,  InDate,  out date and I want to calculate the Date field in between  InDate and outDate because I want to check the Room status any particular date. so How to calculate the Date field.  

Below are the sample Data.

 Eg: If I Select  Date 15-09-2017 so room no 110 Status  Should Show  OCCUPIED and if select  17-07-2017  than room Status should be UNOCCUPIED.

Reg no	room_no	InDate	outDate
922404328	110	07-09-2017	16-09-2017
60105348	111	17-09-2017	18-09-2017
240735168	117	14-09-2017	21-09-2017
380906718	113	19-09-2017	30-09-2017
160530456	109	14-10-2017	18-10-2017
922404328	114	24-10-2017	26-10-2017
961969008	110	26-10-2017	26-10-2017
761813436	113	24-10-2017	27-10-2017
360767610	109	26-10-2017	27-10-2017
260104546	103	30-10-2017	30-10-2017
260104546	102	30-10-2017	31-10-2017
240735168	103	31-10-2017	01-11-2017
20059242	110	01-11-2017	03-11-2017
240735168	113	02-11-2017	04-11-2017

[kaushiknsolanki]

Hi,

Check this links.

https://community.qlik.com/t5/QlikView-Documents/Intervalmatch-example-with-dates/ta-p/1488212

https://community.qlik.com/t5/QlikView-Scripting/Interval-match-2-dates-and-a-range/td-p/594805

Regards,

Kaushik Solanki

[DM106]

Hi Kaushik,

Qvw script which is marked as correct answer in the below link is not loading any data in Qliksense
https://community.qlik.com/t5/QlikView-Scripting/Interval-match-2-dates-and-a-range/td-p/59480

[DM106]

Can somebody please check this post and answer, I am curious to know the answer.

[Lauri]

How about you create an empty variable (let's call it PickDate), then insert a date picker object which stores your date selection in PickDate, and then add a Dimension column to your table:

=If($(PickDate)>=InDate and $(PickDate)<=OutDate, 'Occupied', 'Unoccupied')

Your table appears to have multiple rows per room, so you probably want to do some more calculations to get just one row per room, right?