Solved: Re: time between two events - Qlik Community

Report Inappropriate Content · ‎2017-08-16

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.

	Time	Signal_1	Signal_2	Above 49.8	below 49.8	Latency
	11:42:04	50.0	10.9	11:42:04	-
	11:42:05	50.0	10.9	11:42:05	-
	11:42:08	50.0	10.9	11:42:08	-
	11:42:10	50.0	10.8	11:42:10	-
(A1)	11:42:13	49.8	10.8	-	11:42:13	7
	11:42:14	49.8	10.8	-	11:42:14
	11:42:15	49.8	10.8	-	11:42:15
	11:42:18	49.8	10.8	-	11:42:18
(A2)	11:42:20	49.8	0.0	-	11:42:20
	11:42:22	49.8	0.0	-	11:42:22
	11:42:24	49.8	0.0	-	11:42:24
	11:42:26	49.8	0.0	-	11:42:26
	11:42:28	49.8	0.0	-	11:42:28
	11:42:30	49.8	0.0	-	11:42:30
	11:42:32	49.8	0.0	-	11:42:32
(B1)	11:42:33	49.9	0.0	11:42:33	-	22
	11:42:34	49.9	0.0	11:42:34	-
	11:42:36	49.9	0.0	11:42:36	-
	11:42:39	49.9	0.0	11:42:39	-
	11:42:40	49.9	0.0	11:42:40	-
	11:42:42	49.9	0.0	11:42:42	-
	11:42:43	49.9	0.0	11:42:43	-
	11:42:46	49.9	0.0	11:42:46	-
	11:42:49	49.9	0.0	11:42:49	-
	11:42:52	49.9	0.0	11:42:52	-
(B2)	11:42:55	49.9	4.7	11:42:55	-
	11:42:56	49.9	4.7	11:42:56	-
	11:42:59	49.9	4.7	11:42:59	-
	11:43:02	49.9	4.7	11:43:02	-
	11:43:03	49.9	4.7	11:43:03	-
	11:43:05	49.9	5.2	11:43:05	-
	11:43:08	49.9	5.2	11:43:08	-
	11:43:10	49.9	5.2	11:43:10	-

thank you,

Alex

sunny_talwar · ‎2017-08-17

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;

View solution in original post

sunny_talwar · ‎2017-08-16

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;

Report Inappropriate Content · ‎2017-08-17

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

sunny_talwar · ‎2017-08-17

- 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)

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

Report Inappropriate Content · ‎2017-08-17

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

sunny_talwar · ‎2017-08-17

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?

Report Inappropriate Content · ‎2017-08-17

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)

sunny_talwar · ‎2017-08-17

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;

Report Inappropriate Content · ‎2017-08-21

Thank you Sunny!