8 Replies Latest reply: Aug 21, 2017 3:28 AM by Alex Cirlan RSS

    time between two events

    Alex Cirlan

      Hello,

       

      I have two signals measured every second.

      When Signal_1 is below a certain value Signal_2 should/will change; and i need to measure the latency between the two.

       

       

      In the table below at 11:42:13 Signal_1 is dropping below (or equal) to 49.9 and Signal_2 is following 7 second later (at 11:42:20). Signal 2 can drop to 0 or to a lower value (and i should measure the difference absolute and percentage).

       

       

      Later, at 11:42:33 Signal_1 is going up above 49.8 and Signal 2 is going up again, 22 seconds later at 11:42:55

       

       

      I should also monitor if Signal 2 is moving erratically without Signal_1 to be changing around the 49.8 value.

       

              

      TimeSignal_1Signal_2Above 49.8below 49.8Latency
      11:42:0450.010.911:42:04-
      11:42:0550.010.911:42:05-
      11:42:0850.010.911:42:08-
      11:42:1050.010.811:42:10-
      (A1)11:42:1349.810.8-11:42:137
      11:42:1449.810.8-11:42:14
      11:42:1549.810.8-11:42:15
      11:42:1849.810.8-11:42:18
      (A2)11:42:2049.80.0-11:42:20
      11:42:2249.80.0-11:42:22
      11:42:2449.80.0-11:42:24
      11:42:2649.80.0-11:42:26
      11:42:2849.80.0-11:42:28
      11:42:3049.80.0-11:42:30
      11:42:3249.80.0-11:42:32
      (B1)11:42:3349.90.011:42:33-22
      11:42:3449.90.011:42:34-
      11:42:3649.90.011:42:36-
      11:42:3949.90.011:42:39-
      11:42:4049.90.011:42:40-
      11:42:4249.90.011:42:42-
      11:42:4349.90.011:42:43-
      11:42:4649.90.011:42:46-
      11:42:4949.90.011:42:49-
      11:42:5249.90.011:42:52-
      (B2)11:42:5549.94.711:42:55-
      11:42:5649.94.711:42:56-
      11:42:5949.94.711:42:59-
      11:43:0249.94.711:43:02-
      11:43:0349.94.711:43:03-
      11:43:0549.95.211:43:05-
      11:43:0849.95.211:43:08-
      11:43:1049.95.211:43:10-

       

       

      thank you,

      Alex

        • Re: time between two events
          Sunny Talwar

          May be this

           

          Table:

          LOAD RowNo() as Key,

          *,

          If(Signal_1 > 49.8, Time) as [Above 49.8],

          If(Signal_1 <= 49.8, Time) as [Below 49.8],

          If(Signal_1 > 49.8, 1, 2) as Flag;

          LOAD * INLINE [

              Time, Signal_1, Signal_2

              11:42:04, 50.0, 10.9

              11:42:05, 50.0, 10.9

              11:42:08, 50.0, 10.9

              11:42:10, 50.0, 10.8

              11:42:13, 49.8, 10.8

              11:42:14, 49.8, 10.8

              11:42:15, 49.8, 10.8

              11:42:18, 49.8, 10.8

              11:42:20, 49.8, 0.0

              11:42:22, 49.8, 0.0

              11:42:24, 49.8, 0.0

              11:42:26, 49.8, 0.0

              11:42:28, 49.8, 0.0

              11:42:30, 49.8, 0.0

              11:42:32, 49.8, 0.0

              11:42:33, 49.9, 0.0

              11:42:34, 49.9, 0.0

              11:42:36, 49.9, 0.0

              11:42:39, 49.9, 0.0

              11:42:40, 49.9, 0.0

              11:42:42, 49.9, 0.0

              11:42:43, 49.9, 0.0

              11:42:46, 49.9, 0.0

              11:42:49, 49.9, 0.0

              11:42:52, 49.9, 0.0

              11:42:55, 49.9, 4.7

              11:42:56, 49.9, 4.7

              11:42:59, 49.9, 4.7

              11:43:02, 49.9, 4.7

              11:43:03, 49.9, 4.7

              11:43:05, 49.9, 5.2

              11:43:08, 49.9, 5.2

              11:43:10, 49.9, 5.2

          ];

           

          FinalTable:

          LOAD *,

          Interval(If(Signal_2 <> Previous(Signal_2), Time - New_Time), 's') as Latency;

          LOAD *,

          If(Flag <> Previous(Flag), Time, Peek('New_Time')) as New_Time

          Resident Table;

           

          DROP Table Table;

           

          Capture.PNG

            • Re: time between two events
              Alex Cirlan

              Hi Sunny,

               

              Thank you very much for help!

              I am afraid your script worked partially.

               

              - GOOD: the 7 second measured when the Signal_2 is decreasing and the 22 seconds when Signal_2 is going up again after Signal_1 is above 49.8 - this is working great!


              - NOT SO GOOD: i don't know why the 6 seconds latency appears at 11:42:10... what is that measuring? Signal_1 is stable above 49.8 so the latency should be measured. Sames at second 11:43:05 when i get 32 seconds latency and i do not need it... having this "unneeded" latency measurements will jeopardize the quality of statistics.



              Can you please adjust it to eliminate those useless KPI's?



              Thank you,

              Alex

                • Re: time between two events
                  Sunny Talwar

                  - NOT SO GOOD: i don't know why the 6 seconds latency appears at 11:42:10... what is that measuring? Signal_1 is stable above 49.8 so the latency should be measured. Sames at second 11:43:05 when i get 32 seconds latency and i do not need it... having this "unneeded" latency measurements will jeopardize the quality of statistics.

                  I don't know what it is measuring, I am not a business expert my friend... I just tried to implement the logic I understood from your post... There was a slight error, I have fixed that in the code (seeing 10 instead of 32)

                   

                  Capture.PNG

                   

                  But in the above two scenarios, Signal_2 changed and that is why I thought you needed to see latency? Is that not what you want?

                   

                  Table:

                  LOAD RowNo() as Key,

                  *,

                  If(Signal_1 > 49.8, Time) as [Above 49.8],

                  If(Signal_1 <= 49.8, Time) as [Below 49.8],

                  If(Signal_1 > 49.8, 1, 2) as Flag;

                  LOAD * INLINE [

                      Time, Signal_1, Signal_2

                      11:42:04, 50.0, 10.9

                      11:42:05, 50.0, 10.9

                      11:42:08, 50.0, 10.9

                      11:42:10, 50.0, 10.8

                      11:42:13, 49.8, 10.8

                      11:42:14, 49.8, 10.8

                      11:42:15, 49.8, 10.8

                      11:42:18, 49.8, 10.8

                      11:42:20, 49.8, 0.0

                      11:42:22, 49.8, 0.0

                      11:42:24, 49.8, 0.0

                      11:42:26, 49.8, 0.0

                      11:42:28, 49.8, 0.0

                      11:42:30, 49.8, 0.0

                      11:42:32, 49.8, 0.0

                      11:42:33, 49.9, 0.0

                      11:42:34, 49.9, 0.0

                      11:42:36, 49.9, 0.0

                      11:42:39, 49.9, 0.0

                      11:42:40, 49.9, 0.0

                      11:42:42, 49.9, 0.0

                      11:42:43, 49.9, 0.0

                      11:42:46, 49.9, 0.0

                      11:42:49, 49.9, 0.0

                      11:42:52, 49.9, 0.0

                      11:42:55, 49.9, 4.7

                      11:42:56, 49.9, 4.7

                      11:42:59, 49.9, 4.7

                      11:43:02, 49.9, 4.7

                      11:43:03, 49.9, 4.7

                      11:43:05, 49.9, 5.2

                      11:43:08, 49.9, 5.2

                      11:43:10, 49.9, 5.2

                  ];

                   

                  FinalTable:

                  LOAD *,

                  Interval(If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time')), 's') as Latency;

                  LOAD *,

                  If(Flag <> Previous(Flag) or Signal_2 <> Previous(Signal_2), Time, Peek('New_Time')) as New_Time

                  Resident Table;

                   

                  DROP Table Table;

                   

                  Can you explain the business logic so that I can fix this for you.

                   

                  Best,

                  Sunny

                    • Re: time between two events
                      Alex Cirlan

                      Hi Sunny,

                       

                      The business logic is the following:

                       

                      1) As time as Signal_1 is above 49.8 everything is fine and Signal_2 can be at any level... i do not measure the speed of change in Signal_2 (if is changing)

                       

                      2) When Signal_1 is going down below 49.8 I need to measure how fast Signal_2 is decreasing or even stopping (ideal one counter for decreasing at least X% with X a variable in my Qlik file)

                       

                      3) When Signal_2 is going up above 49.8 I need to measure how fast Signal_2 is going up (no of seconds for S2 to recover once S1 is back above 49.8)

                       

                      4) Signal_2 should never grow if Signal_1 is below 49.8 (ideally should be zero)

                      Best,

                      Alex

                        • Re: time between two events
                          Sunny Talwar

                          In other words, are you saying that you would want to see the first change in Signal_2 after Signal_1 has changed? Is this what you are trying to get?

                            • Re: time between two events
                              Alex Cirlan

                              Yes! This is my number 1 priority:

                              1. What is the latency of S2 after S1 dropped below 49.8

                              2. What is the latency of S2 after S1 come back above 49.8

                               

                              Secondly I would like to monitor if Signal_2 is growing even if signal_1 is below 49.8 (which shoudn't happen)

                                • Re: time between two events
                                  Sunny Talwar

                                  There might be a better way to do this... but this is what I have for you....

                                   

                                  Table:

                                  LOAD RowNo() as Key,

                                  *,

                                  If(Flag = Previous(Flag), Peek('NewFlag'), RangeSum(Peek('NewFlag'), 1)) as NewFlag;

                                  LOAD *,

                                  If(Signal_1 > 49.8, Time) as [Above 49.8],

                                  If(Signal_1 <= 49.8, Time) as [Below 49.8],

                                  If(Signal_1 > 49.8, 1, 2) as Flag;

                                  LOAD * INLINE [

                                      Time, Signal_1, Signal_2

                                      11:42:04, 50.0, 10.9

                                      11:42:05, 50.0, 10.9

                                      11:42:08, 50.0, 10.9

                                      11:42:10, 50.0, 10.8

                                      11:42:13, 49.8, 10.8

                                      11:42:14, 49.8, 10.8

                                      11:42:15, 49.8, 10.8

                                      11:42:18, 49.8, 10.8

                                      11:42:20, 49.8, 0.0

                                      11:42:22, 49.8, 0.0

                                      11:42:24, 49.8, 0.0

                                      11:42:26, 49.8, 0.0

                                      11:42:28, 49.8, 0.0

                                      11:42:30, 49.8, 0.0

                                      11:42:32, 49.8, 0.0

                                      11:42:33, 49.9, 0.0

                                      11:42:34, 49.9, 0.0

                                      11:42:36, 49.9, 0.0

                                      11:42:39, 49.9, 0.0

                                      11:42:40, 49.9, 0.0

                                      11:42:42, 49.9, 0.0

                                      11:42:43, 49.9, 0.0

                                      11:42:46, 49.9, 0.0

                                      11:42:49, 49.9, 0.0

                                      11:42:52, 49.9, 0.0

                                      11:42:55, 49.9, 4.7

                                      11:42:56, 49.9, 4.7

                                      11:42:59, 49.9, 4.7

                                      11:43:02, 49.9, 4.7

                                      11:43:03, 49.9, 4.7

                                      11:43:05, 49.9, 5.2

                                      11:43:08, 49.9, 5.2

                                      11:43:10, 49.9, 5.2

                                  ];

                                   

                                  FinalTable:

                                  LOAD *,

                                  If(Previous(TempLatency) > 0, Null(), Latency) as Latency_Final;

                                  LOAD *,

                                  Interval(If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time'))), 's') as Latency,

                                  If(Len(Trim(If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time'))))) = 0 and Flag = Previous(Flag), Peek('TempLatency'), If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time')))) as TempLatency;

                                  LOAD *,

                                  If(Flag <> Previous(Flag) or Signal_2 <> Previous(Signal_2), Time, Peek('New_Time')) as New_Time

                                  Resident Table;

                                   

                                  DROP Fields New_Time, TempLatency, Latency, NewFlag, Flag;

                                  RENAME Field Latency_Final to Latency;

                                   

                                  DROP Table Table;

                                   

                                  Capture.PNG