Room Usage

[joshua_c_clifto]

I have a list of conference rooms in a building, and I need to calculate the percentage of hours each room was utilized for a given month relative to the total available hours for that room. I've pulled the data from our SharePoint calendar for the last 7 years so there are 6000-some meetings. The problem I'm running into is that some of these meetings are recurring so when I load the script, the table is not accounting for meetings that take place every week, every 2 weeks, etc. I was hoping, based on how I currently have my excel sheet structured, there was a correct way I can do this, possibly with the numbered occurrences column? I've attached a small sample of what my data looks like, I can also provide the load script I was using if necessary...thank you!

[rwunderlich]

Here's a script to get you started.  The key point is using IterNo() to process the repeats.

Meetings:
LOAD
RecNo() as RecId,
ID,
EventType,
Recurrence,
EventState,
Frequency,
Occurences,
Day,
Timestamp([Start Time] + ((IterNo()-1) * 7)) as [Start Time],
if(EventType = 'Recurring'
,Timestamp([Start Time] + ((IterNo()-1) * 7) + MeetingLength)
,[End Time]
) as [End Time],
BeginHour,
EndHour,
MeetingLength,
Minutes,
Room
FROM
[..\..\..\Downloads\QLIK Data.xlsx]
(ooxml, embedded labels, table is Sheet1)
While IterNo() <= Occurences
;

You may have to make some adjustments for your full data, such as using AddMonth([Start Time], (IterNo()-1)) for the Annual meetings etc. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com