7 Replies Latest reply: May 9, 2011 4:13 AM by Deepak Vadithala RSS

    Analyze frequency of contact



      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?




        • Analyze frequency of contact
          Deepak Vadithala

          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

            • Analyze frequency of contact

              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?


                • Analyze frequency of contact
                  Dennis Hoogenboom

                  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.

                • Re: Analyze frequency of contact

                  Hi DV,

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



                    • Re: Analyze frequency of contact
                      Deepak Vadithala

                      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