6 Replies Latest reply on Sep 18, 2017 9:12 AM by Motorola Licenses

# Calculation query - Dynamic values

Hello,

I need help to calculate downtime with the below criteria  for the attached workbook:

Timestamp - mm/dd/yyyy hh:mm:ss

Severity - Critical and Clear

Downtime to be calculated

If 'hh' value for critical and clear match, then downtime = timestamp(Clear) minus timestamp(Critical)

If 'hh' dont match, then

Downtime for Critical record = (hh+1 from critical timestamp) - (Critical timestamp)

and

Downtime for Clear record = (Clear Timestamp) - (hh value from clear timestamp)

• ###### 1. Re: Calculation query - Dynamic values

I am not seeing any "Key" field to link "Critical" and "Clear".

I don't think you cannot judge the criteria without that key because I don't know which critical matches to which Clear....?

Thanks,

Shin

• ###### 2. Re: Calculation query - Dynamic values

for example

device 1 -

went down at 8/1/2017  6:02:36 AM and came back up at 8/1/2017  6:05:02 AM.

downtime for this would be 6:05-6:02

can you define a key to make this work?

• ###### 3. Re: Calculation query - Dynamic values

critical will always be followed by clear for the same device

• ###### 4. Re: Calculation query - Dynamic values

Hi ML,

Not very sophisticated;) but find my approach as reference below and stored in attached workbook version 10.3

1. lookup: LOOKUP(attr([Timestamp]),1)

2. downtime yes/no: if attr([Severity])='Critical' then datediff('second',attr([Timestamp]),[Lookup]) else 0 END

3. downtime: window_sum([downtime yes/no])

Regards,

Norbert

• ###### 5. Re: Calculation query - Dynamic values

Very troublesome table calculation should be set.

I created test data.

Summary

Thanks,

Shin