Peak Load of Call Detail Records (overlapping timespans)

[#1]

Hi everyone,

I am failry new with qlikview and I think I am stuck.

I have call detail records from a videoconferencing platform like this:

Id, objSystemId, startTime, endTime

1, 404, 2011-01-20 20:10, 2011-01-20 21:00

2 800, 2011-01-20 19:40, 2011-01-20 20:30

3 800, 2011-01-20 19:50, 2011-01-20 21:00

....

There are lots of them over the past years. Approx. 400k

objSystemId is the device that was in the call.

I want to be able to tell the peak simultaneous call load of a objSystemId in a timespan. I.E. last week.

In other words: the maximum number of simultaneous calls for objSystemId 800 during last week was 35.

My Graph is supposed to show the objSystemId on the x-axis and the max load on the y-axis.

Which is easy once I have a table with correct data 😉

My first approach was to compare every call with every call which was way too much.

My second to use intervalmatch which rsults I did not understand 100%.

Could someone write some sample code I could work with?

Thank You!!!

[#2]

Hi again,

I didn't make any progress so far. 😞 but just for clarification. I don't want anyone to do the work for me, but PLEASE point me into the right direction.

I am stuck 😞

[tresesco]

probably you can try this sort of logic.

Count (If(Week(DateField)=Week(Today()),ObjSystemId))

Regards, tresesco

[#4]

Hi treseco,

thanks the reply!

Unfortunately this won't help, as it gives all calls of current week and not only the calls that were simultaenous.

I need peak parallel calls

[tresesco]

Sorry, i didn't get you. how do you decide, if the calls are Simultaneous?

Regards, tresesco

[#6]

no prob.

each call has a start and an end as soon as they overlap, they are simultaneous.

i.E.

1 9am to 10 am

2 9:30am to 11 am

these times overlap so 1 and 2 are simultaneous.

Now I want to see: When were the peak overlapping times? Or When happend maximum overlapping.

Background;: Every call needs a resource which is limited as each call needs a port on the system. The system can take 40 calls at the same time. I want to find out when I am getting close to maximum system load.

Got it?

[tresesco]

thanks, the explanation is fair enough. . i would get back after a sort of work out.

[#8]

okidoke

I am currently sitting at it. its driving me nuts. 😞

[tresesco]

yes, intervalmatch would be the way to solution; with a tricky approach. find the attached application.

Hopefully, this would help you.

[#10]

YOU ARE THE MAN!!!!

thank you very much!!! this works well for me.

EXACTLY what I wanted!