Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
moh2_sid
Contributor II
Contributor II

Calc freq count for monitoring app based on latest rows and aggr function

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:

  • 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 

 

Labels (1)
0 Replies