Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
- 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
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
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?
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)
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;
Thank you Sunny!