Thanks for your reply.
I have a table with holiday apartment booking details and I would like to have a table in QlikView showing Occupancy % by month per apartment. So for each month I need a percentage of nights booked over the total for that month.
Hope this gives you a bit more clarity.
one way to do so might be:
tabBookings: LOAD RecNo() as BookID, * Inline [ AptID, Start, End A, 01/02/2009, 01/02/2009 A, 01/16/2009, 03/27/2009 A, 04/25/2009, 07/12/2009 B, 02/03/2009, 03/11/2009 B, 03/18/2009, 08/21/2009 ]; tabMonthOccup: LOAD *, Num(DaysBooked/DaysInMonth,'0%') as Occupancy; LOAD *, MonthName(MonthBookStart) as MonthBook, MonthBookEnd-MonthBookStart+1 as DaysBooked, MonthStart(MonthBookStart,1)-MonthStart(MonthBookStart) as DaysInMonth; LOAD BookID, Date(RangeMax(Start,MonthStart(Start,IterNo()-1))) as MonthBookStart, Date(RangeMin(End ,MonthStart(Start,IterNo())-1)) as MonthBookEnd Resident tabBookings While MonthStart(Start,IterNo()-1)<=End;
I would suggest to go with John's solution however, as it should deliver a far more flexible result especially when adding some additional calendar fields (drill down capabilities, responsiveness to date related selections ...).
hope this helps
QlikCommunity_Thread_238292.qvw 171.0 K
Set analysis isn't responsive to the values of your dimensions in the way it seems you want here. You'll need another solution. Assuming your Start and End are values in some table that has some other key...
Name, Start, End
...a probably common way of doing it is to create a table that has one row for each key and each date between start and end for that key.
Connect your Date to a master calendar. Then your expression is just this:
That is what I propose, yes, but it's simpler than I suspect you are imagining. See attached. Script and chart below.
,recno() as RentalID
,Start+iterno()-1 as Date
WHILE Start+iterno()-1 <= End
,monthstart(Date) as Month
LOAD makedate(2008,12,31)+recno() as Date
The potential problem is memory. If you're tracking tens of thousands of apartments over the past 30 years, say, this would yield a very large table. It can be dramatically shrunk down if that's a problem, but doing so IS complicated. Maybe I should write a subroutine that builds the smaller but more complicated version automatically - give it a table, a key field, the Start and End fields, and the field name for the date it produces, and it builds the whole data structure and all the data for you. Something like that. Hmmm. Hmmm. Probably more time than I can devote right now, and you probably don't need it. It's hard for me to imagine you tracking enough apartments over a long enough time frame to cause a real problem.
ApartmentOccupancy.qvw 159.2 K