Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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?
I meant what would be the correct expression for that set analysis or what would need to be done in the script?
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.
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).
Here is the attachment
Check if attached sample can help.
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.
Included timestamp dimension and line chart, corrected bug in duration calculation -