Time difference DQL

[RAKESHNREDDY]

Hello Everyone,

Good day,

I have a requirement to calculate the time difference between the minimum and first maximum points for all the available data within the timeframe selected. In this example, the time taken for the value to reach 98 from 0.  I'm able to get the latest output using the below DQL. Is there any trick to getting the output of the complete timeframe we selected?

DQL Used

timeseries count = max(appl.completed), timestamp=start()
| fieldsAdd d=record(count=count[], timestamp=timestamp[])
| fieldsAdd d = arrayRemoveNulls( iCollectArray( if(d[][count]>97, d[] )))
| expand d
| fieldsAdd e=record(count1=count[], timestamp1=timestamp[])
| fieldsAdd e = arrayRemoveNulls( iCollectArray( if(e[][count1]<1 AND e[][timestamp1] < d[timestamp], e[] )))
| expand e
| summarize d=takeFirst(d), e=takeLast(e)
| fieldsAdd m = d[timestamp], n = e[timestamp1]
| fieldsAdd A = m - n
| fields A

[mark_bley]

Here is an example calculating duration, hope it helps:

data 
record(content="79895474545006\tPreProcessing\tstart\t1\t\t2023-04-27 04:23:12.000.153"),
record(content="79895474545006\tPreProcessing\tend\t1\t\t2023-04-27 04:30:38.000.153"),
record(content="79895474545005\tPreProcessing\tstart\t1\t\t2023-04-26 05:30:38.000.1538"),
record(content="79895474545005\tPreProcessing\tend\t1\t\t2023-04-27 04:30:38.000.153")
| parse content, """ALNUM:batch.id '\t' STRING:stage  '\t'  LDATA:stage_status '\t' INT?:valid '\t' FLOAT?:result PUNCT? '\t' TIMESTAMP('yyyy-MM-DD HH:mm:ss'):timestamp"""
| filter stage == "PreProcessing"
| summarize {start=takeFirst(if(stage_status=="start",timestamp)), end=takeFirst(if(stage_status=="end",timestamp))}, by:{batch.id, stage} 
| fieldsAdd duration = end - start

[RAKESHNREDDY]

Thanks Mark, tried including the 'By: Day' with a few modifications in the summarize. Works fine now. 

[marco_irmer]

Perhaps something like this would work for you:

timeseries count = max(appl.completed)
| fieldsAdd minVal = arrayMin(count), maxVal = arrayMax(count)
| fieldsAdd minIndex = arrayLastIndexOf(count, minVal), 
            maxIndex = (arraySize(count) -1) - arrayLastIndexOf(arrayReverse(count), maxVal)
| fieldsAdd timeDistance =(maxIndex - minIndex) * interval