Analyze frequency of contact

[qw_johan]

Hi,

First…I have only been using Qlikview for a few months and haven't really done anything too advanced.

I want to analyze the frequency between two fields 'Outbound' and 'Inbound'. Both of these fields contains
ip addresses. Also there is a 'timestamp' field and a 'duration' of time field.
What I would like to do is to show which ip addresses have the most contact?
How do I do that? Any ideas?
Can this be displayed in a pivot table ?

Also, there is the timestamp ... how can I use that?
I am thinking in terms intensity of contact between two ip addresses over a day or a week or a month … ?
Or show times when there is no contact...how would you go about this? Ideas?
Is this possible?

Thanks,

Johan

[IAMDV]

Johan - This is definitely possible. It will be easy if you can post the QV document with some data. We can work out an example for your understanding.

Cheers - DV

[qw_johan]

Hi DV!

I am glad to hear that it is possible. I am curious to know how it is done.
I've attached a qvw file with my data (outbound and inbound fields are scrambled by Qlikview).

How is frequency best displyed for a user? Chart (pie, line..) or a pivot table etc......?

Duration is sometimes '0' seconds...Is it possible to see frequency not only on all data
but also filter out those that are '0' seconds thus only show frequency on connections that
are longer than '0' seconds?

Thanks

[Anonymous]

Hi Johan,

When you want to work with day's it is easier to make a date field (without the hours and seconds) in your loading script. To do this you can add this line to you script:

Date(Floor(TimeStamp) as Date,

But this only creates dates if there is a TimeStamp of that day.
If you also want to show dates which are not in your datafield you need to make a calender which generates also the "missing" dates.

[qw_johan]

Hi DV,

I am really eager to know if you have been able to figure someting out....

Thanks,

Johan

[IAMDV · Accepted solution]

Hi Johan,

Sorry for the delay in replying back. I am very busy with a deadline and QV community was offline for few days. Thanks for posting the data.

Below are some ideas on what you can do with your data...

Highest Inbound IPAddress Count        :      max(aggr(count( Inbound),Inbound))

Highest Outbound IPAddress Count     :     max(aggr(count( Outbound),Outbound))

Total Duration for Inbound IPAddress    :     SUM(AGGR(COUNT( Outbound),Duration))

Use the Inbound IPAddress as Dimension to show Sum of Duration for each distinct IPAddress...

You can use the TimeStamp field to show the trend of duration. I mean peak & offpeak timings. Please convert the TimeStamp format into Date (As suggested by Dennisnet). Then you can show the daily trend of the duration. Probably you can show top 10 Inbound & Outbound IPAddresses for any given time in the Table format. I mean provide list boxes to select the Dates & TimePeriod and users can select a specific times to see the top 10 IPAddresses.

There are loads of ideas on how we can visualize the data. However, It is very important to understand the end user requirement. So please try the above ideas and do let me know if you need any specific help on expressions or charts.

Good luck!

Cheers - DV

[qw_johan]

Thank you very much. Lots of good information for me to work with.

[IAMDV]

Hi Johan,

I am glad that the information was helpful. Let me know if you have any specific questions with Expressions.

Also please can you mark the post Answered/ Helpful, This will be very useful for other users.

Cheers - DV