Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
fikristar
Creator
Creator

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?

fikristar
Creator
Creator
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?

fikristar
Creator
Creator
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.

fikristar
Creator
Creator
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).



fikristar
Creator
Creator
Author

Here is the attachment

Digvijay_Singh
Master III
Master III

Check if attached sample can help.

Digvijay_Singh
Master III
Master III

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
Master III
Master III

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