4 Replies Latest reply on Oct 3, 2018 2:23 PM by Joe Oppelt

# Need help on a LOD calculation

Hi,

I am working on room usage report. I want to calculate total minutes booked in weekdays and weekends. In the sample workbook attached, event 1 has two sections that take place in the same room, on the same day of the week and at the same time period, it should be counted once (170 mins usage).  The result I am looking for is as below.

Facility Id      Total_Weekend_Mins    Total_Weekday_Mins

CD-134                   180                   1,155

 Facility Id Total_Weekend_Mins Total_Weekday_Mins CD-134 180 1,155

Thank you for any help!

Kate

• ###### 1. Re: Need help on a LOD calculation

Lots of possible scenarios.

What would you do if one section (same day-time-room) had a different value from a second such session?

Could there be two sessions for an event on the same day and room, but at different times?

We can use LOD to handle those multiple values.  I would set up the LOD differently though, depending on how things could happen.  (I could just plow in there and eliminate the second value, but I would rather have something that handles all similar situations.)

• ###### 2. Re: Need help on a LOD calculation

Just one idea.

Thanks,

Shin

• ###### 3. Re: Need help on a LOD calculation

Hi Joe,

If there are two sessions for an event on the same day and room, but at different times, then the duration will be sum up. Only when the event name, room, date of week, and meeting pattern (the meeting start time and end time) are exactly the same, it only needs to be counted once for the room usage.

Kate

• ###### 4. Re: Need help on a LOD calculation

In the attached I made Calculation1.  This eliminates duplicate values when two sessions are identical.

On the sheet I added my calc to TEXT.  And I removed Session from the sheet.  You can see that you'll just get 170 for that one event.

Using that calc instead of your original measure will let you do the summing and other math you need to do.