Calc freq count for Data based on latest rows only and a Constant/Similar Column Value

[moh2_sid]

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/2024 0:00:05
2, abc, 1, 8/8/2024 0:00:05
3, XYX, 1, 8/8/2024 0:00:15
4, abc, 1, 8/8/2024 0:00:15
5, XYX, 1, 8/8/2024 0:00:30
6, abc, 1, 8/8/2024 0:00:30
7, XYX, 4, 8/8/2024 0:00:40
8, abc, 4, 8/8/2024 0:00:40
9, XYX, 4, 8/8/2024 0:00:50
10, abc, 4, 8/8/2024 0:00:50
11, XYX, 4, 8/8/2024 0:01:00
12, XYX, 4, 8/8/2024 0:01:05
13, abc, 4, 8/8/2024 0:01:05
14, XYX, 4, 8/8/2024 0:01:15
15, XYX, 1, 8/8/2024 0:01:20
16, abc, 3, 8/8/2024 0:01:20
16, abc, 3, 8/8/2024 0:01:25
18, XYX, 1, 8/8/2024 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

abc      3       2       0:01:20    0:01:25  0:05
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:

• Count: aggr(Count(ID), Lag, Topic)
• Min Time: aggr(min(timeStamp2), Lag, Topic)
• Max Time: aggr(max(timeStamp2), Lag, Topic)

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