22 Replies Latest reply: Sep 22, 2016 3:50 PM by Fikri Shihab RSS

    aggregating maximum value with set analysis

    Fikri Shihab

      I have time based data that contains a few key columns as follows:

       

      Time Stamp, device_id, status, latency, region, duration. Duration unit here is in seconds.

       

      The status of the device could be in network or out of network.

       

      The of status and duration indicate the status of the terminal, either in network or out of network and the duration simply shows the duration of that device either in network or out of network.

       

      I'd like to create a chart showing total duration (in hours) of the device id when it is still considered in the network but the latency value is zero or blank. As it is reported periodically duration value keeps changing, so I'd like to select the maximum duration only for each device for each observation period.  Tried set analysis like below with max value but it is not working.

       

      sum({$<latency={"=len(trim(terminal_latency))=0"}, [status]={'*IN*'},  duration = {$(=max(duration))},[device_id]>}[duration])/3600


      What is the correct expression for this?  should this be better with advanced aggregation? Any suggestion?