sum of max numbers

[Anonymous]

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

Column A : Date

Column B: Time

Column C: Network ID

Column 😧 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!

[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

Regards

Raman

[Anonymous]

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.

[MarcoWedel]

please post some sample data and your expected output

thanks

regards

Marco

[Anonymous]

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>

[Anonymous]

HI Qlik experts,

Any suggestions or inputs on this?