Lag and firstValue are not working as I had hoped

Question & Answer
1

I have a bunch of records counting device failure attempts. The data looks like this:

Device Timestamp Failure Code Result
1A:2B:C3 2023-03-10 17 Activation Failed
1A:2B:C3 2023-03-11 Null Activation Successful
2B:5N:F2 2023-03-01 Null Activation Successful
1D:3F:E2 2023-03-02 27 Activation Failed
1D:3F:E2 2023-03-03 15 Activation Failed

Based upon this data, what I need to report on for my dashboard is a list of Failure Codes which were followed by an attempt by the same device and succeeded. Ultimately what I need to determine a ranking of error codes which occurred and were followed by a successful start up.

In the above example, 17 error happened prior to a successful activation the next day. In my entire table I may have hundreds or thousands of times where this condition occurs. I just want to report on the error code and the number of times that this “successful retry” happens. Current dashboards require me to list deviceID, timestamp,etc for each device in order for me to see the previous error code. I am finding no way to get a counter on how many successful retries this is occurring.

My goal is to provide my boss a list of error codes like this:

Error Code Occurrences % among error codes
17 60 51.2%
18 10 12.0%
27 8 8.7%
9 5 4.0%

Any suggestions for a newbie? Again, i have tried lag, firstValue, LastValue, etc. already unsuccessfully.

Thanks for your assistance!

2

Hey @PaulH! Hopefully you will hear some suggestions from the Community soon. Just in case, I have pinged one of our SAs to see if they can reply back on Monday! Hope you are having a good weekend! :slight_smile:

1 Like
3

Does something like this work?

ifelse(firstValue({Result}, [ {Timestamp} ASC ],[ {Device } ] )='Successful' AND lag({Result}, [ {Timestamp} ASC ],1,[ {Device} ] )='Failed',count({attributes_url}),sum(0))