Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am working on a measure to calculate the count or frequency of IDs for a topic based on the Lag column. Please review the sample data below.
Sample Data:
ID | Topic | Lag | timeStamp2 |
---|---|---|---|
1 | XYX | 1 | 8/8/24 0:00:05 |
2 | XYX | 1 | 8/8/24 0:00:10 |
3 | XYX | 1 | 8/8/24 0:00:15 |
4 | XYX | 1 | 8/8/24 0:00:20 |
5 | XYX | 1 | 8/8/24 0:00:30 |
6 | XYX | 1 | 8/8/24 0:00:35 |
7 | XYX | 4 | 8/8/24 0:00:40 |
8 | XYX | 4 | 8/8/24 0:00:45 |
9 | XYX | 4 | 8/8/24 0:00:50 |
10 | XYX | 4 | 8/8/24 0:00:55 |
11 | XYX | 4 | 8/8/24 0:01:00 |
12 | XYX | 4 | 8/8/24 0:01:05 |
13 | XYX | 4 | 8/8/24 0:01:10 |
14 | XYX | 4 | 8/8/24 0:01:15 |
15 | XYX | 1 | 8/8/24 0:01:20 |
16 | XYX | 1 | 8/8/24 0:01:25 |
17 | XYX | 1 | 8/8/24 0:01:30 |
Expected Results:
Topic | Lag | Count | Min Time | Max Time | Duration (Mins: Secs) |
---|---|---|---|---|---|
XYX | 1 | 3 | 0:01:20 | 0:01:30 | 0:10 |
Current Results:
Topic | Lag | Count | Min Time | Max Time | Duration (mm:ss) |
---|---|---|---|---|---|
XYX | 1 | 9 | 8/8/2024 0:00:05 | 8/8/2024 0:01:30 | 01:25 |
XYX | 4 | 8 | 8/8/2024 0:00:40 | 8/8/2024 0:01:15 | 00:35 |
Expressions Used in Front End:
As you can see, the current expressions considers all rows with Lag = 1 even after the Lag value changes to 4 and then to 1, my requirement is only to consider latest rows with same lag values. If the Lag Value changes I do not wish to consider the others rows even with the same Lag Value.
Can the experts provide the correct expression to only include the latest rows (based on timeStamp2) that have a constant Lag value? Once the Lag value changes, later rows must not be included.
I hope this explanation helps clarify my requirements, but please let me know if further clarification is needed.
Thanks for your assistance.
@Kushal_Chawda
@Vegar
@sunny_talwar