Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

QlikSense-Telephone Call Concurrency based on Start and End Time

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?

4 Replies
Highlighted
Master III
Master III

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

Highlighted
Master III
Master III

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.

Don't allow Perfection to be the enemy of Good Enough.
May you live in interesting times!
Highlighted
Contributor II
Contributor II

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!

Highlighted
Contributor II
Contributor II

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