Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

aggregating maximum value with set analysis

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? 

22 Replies
sunny_talwar

So you want max duration change based on different dimension? If that is true that set analysis may not work for you. or it might if you are wiling to make changes in the script?

Anonymous
Not applicable
Author

I'd like to aggregate the maximum duration for all devices for selected period of time (e.g. hour, day, etc.). My dimension in x axis is simply either one of these two

=Date(tpa_date, 'DD-MM-YYYY')

or

Timestamp((Ceil([Time Stamp],1/IntervalSize)),'DD-MMM-hh:mm')  for dynamic interval size

Could you tell me what is the correct one?

Anonymous
Not applicable
Author

I meant what would be the correct expression for that set analysis or what would need to be done in the script?

sunny_talwar

I think it would be easier if you can provide a sample with the expected output to try few things before I can say that this is the solution which will work for you.

Anonymous
Not applicable
Author

Please find attached the sample file.

BTW, Since date and time is in separate column I made another filed namely Time Stamp in the script

Timestamp(Floor(Timestamp(Date&' '&Time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp], 


And also extracted the duration from the status and converted it seconds (e.g. IN_NETWORK 1 h 57 min 50 sec becomes 1*3600+57*60+50 sec)


I can't provide the expected output as this is what I'd like to see.


I simply want to select the maximum value for the duration when the status is in network and latency is blank or null sum these max values for each customer_id in the desired period (e.g. one day).



Anonymous
Not applicable
Author

Here is the attachment

Digvijay_Singh

Check if attached sample can help.

Digvijay_Singh

It calculates duration in seconds in script for IN_Network status, I am confused which dimension you want to keep in chart out of customer_id, Date, network Id( I think you mentioned it a device id?). But I am sure based on this sample you can ask specific questions now.

Digvijay_Singh

Included timestamp dimension and line chart, corrected bug in duration calculation -