5 Replies Latest reply: Aug 14, 2016 2:39 PM by Fikri Shihab RSS

    sum of max numbers

    Fikri Shihab

      I have a Table that contains a few columns like this:


      Column A : Date

      Column B: Time

      Column C: Network ID

      Column D: Customer ID

      Column E: Customer status

      Column F: Time (in sec)

      Column G: Region (Region A, B, C....etc.)


      Customer status (E) denotes the status of the customer, which could be two possible values; IN Network or Out of Network.

      Column F (time) indicates the duration of customer in the network or out of network. keeps changing.


      The data is something like log file where column F (Time in sec) for each customer keep changing. Normally it increases over the time showing how long the customer in network or out of network.  .

      I'd like to draw a chart with time dimension (date or certain time interval) with the expression that shows sum of maximum total duration (in hour) for each customer  where its status (column D) is out of network and total duration for the interval under observation is less than 1 hour. So, in other words for each customer I'd like to take only the maximum value only when sum of total duration (for each day) is less than 1 hour.

      The expression will show the total duration of outage (out of network) for all terminals (in hour) over the time (e.g. day).

      I tried some set analysis and regular expressions with if and max but they don't k give me the result I expected.


      Any suggestion or help would be helpful...

      Thank you!

        • Re: sum of max numbers
          raman rastogi

          hi shihab,


          Use this expression May help you ,

          =if(Time< 1,MAX(aggr(Sum({<Customer Status={'Out of Network'}>}Time),CustomerID))


          * Based on time Format change value of Time



            • Re: sum of max numbers
              Fikri Shihab

              Hi Raman,


              Thanks but this is not showing any chart when I use it in my expression.  Btw, column F should be read as duration instead of Time (my bad) and the results I'd like is in hour, so I slightly modified it as follows:


              =if(Duration< 1,MAX(aggr(Sum({<Customer Status={'Out of Network'}>}Duration),CustomerID)/3600)


              However, no value and cart is showing up although the expression shows OK.  Any hint or other suggestions?  May I know why in your script the Duration is <1? 


              The requirement that I have is to sum all duration for each customer if its total outage duration is less than 1 hour (e.g. <3600 seconds).  Please note that the unit in Duration in column F is in second.

            • Re: sum of max numbers
              Marco Wedel

              please post some sample data and your expected output







                • Re: sum of max numbers
                  Fikri Shihab

                  Hi Marco and others,


                  Sorry for the delay.


                  Please find attached the data sample. This is a pre-processed data for 1

                  hour interval generated with straight table.

                  Time in sec is the duration of a customer out of network or in_network.


                  The expression used for  the column with name less than 5 min is


                  =sum(if((<=300 and Status='IN_NETWORK'), 1,0))

                  and the column for between 5-10 min is =sum(if((>300 and

                  (< 600 and Status='IN_NETWORK'), 1,0))


                  Total duration for each customer is suppose to be growing, if it in network

                  or out of network. Of course, it could go from out of network to in network

                  and then then number starts growing again.


                  What I'd like to show is a table (additional columns) and a chart showing

                  the  sum of total duration where the customers in network or out of network

                  for the interested interval (e.g. a day).  As the duration keeps going up

                  this would be the maximum of duration for each day, not each record is

                  added as it will give incorrect result.


                  Further to this, I'd like also to be able to draw a chart showing the

                  duration of out of network (only when total duration of time in sec is less

                  than one hour (3600 sec) for a day ) and for in network (no filter - just

                  max for that particular day).


                  Any hint to this would be very helpful.


                  Thank you



                  On Wed, Aug 10, 2016 at 6:27 AM, Marco Wedel <qcwebmaster@qlikview.com>