QlikSense-Telephone Call Concurrency based on Start and End Time

[Anonymous]

Good day,

I am new to Qlik and have been posed with a challenge that I cannot get my head around.

I have daily call data records that contain call start and end times. The Customer would like to know at any point in time in a day what the maximum number of Calls occurring at the same time is to check for sizing requirements.

Start Time  -  End Time

10:00:23     -    10:01:28 Call 1

10:01:10     -    10:20:51 Call 2

10:01:12     -    10:23:23 Call 3

10:24:01     -    10:24:21 Call 4

Max Call Concurrency = 3

The files are 24 hour files and based on PBX Customers.

Has anybody had some experience with this?

[antoniotiman]

Hi Craig,

may be this

Temp:
LOAD * Inline [
StartTime , EndTime,Call
10:00:23,10:01:28,1
10:01:10,10:20:51,2
10:01:12,10:23:23,3
10:24:01,10:24:21,4
09:01:01,10:01:01,5
];

LOAD *,
If(StartTime >= Peek(StartTime) and StartTime <= Peek(EndTime),RangeSum(Peek(Count),1),1) as Count
Resident Temp
Order By StartTime;
Drop Table Temp;

and Max Concurrency = Max(Count)

Regards,

Antonio

[oknotsen]

Though very memory hungry during the process of creating the table, you could do this with an interval match.

IntervalMatch ‒ Qlik Sense‌

1. Figure out how your customer defines "any point in time". Is that once an hour, every minute, every second?
2. Generate a helper table that contains those moments.
3. Do an interval match between that helper table and your CDRs. Warning: This will create a freakingly huge table if you have a lot of long calls and your customer insists on "every second".
4. Now do a count the number of occurrences of every moment.

If your customer wants to be able to slice-and-dice this data, you have to keep this huge table in your data model. But if not, you can save yourself a lot of memory by making a new table with only the moment and the amount that values shows up and drop the huge table.

I hope the above makes sense.

[Anonymous]

Hi Antonio,

Thank you for your reply, this is a very interesting line of code, I will break this down so I can understand the logic. In the Interim I got my app working and the outputs look good.

It seems if I can get the same results from your logic then it will be much simpler so I will look into this.

Thanks very much!

[Anonymous]

Thanks Onno

I have got my app working using the method you explain in your message.

Thanks for your response!

I will let you know if I need more assistance