Aggr & Set Analyis

[shayd]

Hi all,

I have the following table:

IncidentID	Queue_Start_Date	Queue_Num
F0F98C23-BD60-E711-8D3D-0050569E2C95	18/2/2021 8:07	1
F0F98C23-BD60-E711-8D3D-0050569E2C95	18/2/2021 9:28	2
F0F98C23-BD60-E711-8D3D-0050569E2C95	18/2/2021 9:38	3
9E0DF7AC-8114-E711-A3AF-0050569E2C95	17/6/2020 8:21	1
9E0DF7AC-8114-E711-A3AF-0050569E2C95	17/6/2020 8:37	2
88BBB488-86A4-EA11-8064-0050569E4450	11/6/2020 6:43	1
5A441CC2-89A4-EA11-8064-0050569E4450	2/6/2020 4:37	1
3C9A079F-8AA4-EA11-8064-0050569E4450	2/6/2020 4:38	1

The Queue_Num field basically counts how many times IncidentID appears in the table(row_number).
I want to count how many IncidentID's I have that their Queue_Num's count is not higher then 1 (I have 3, rows 6,7 & 8).
How many IncidentID's I have that their Queue_Num's count is not higher then 2 (I have 2, rows 4 and onwards).
How many IncidentID's I have that their Queue_Num's count is higher then 2 (I have 1, rows 1,2 & 3).

Is there a way to do that using aggr + set analysis on UI?

Thanks!

[tresesco · Accepted solution]

I hope you didn't really mean to use aggr+set analysis, and it would be fine if set analysis alone could help you. 😋

For your first problem, try like:

=Count(DISTINCT {<IncidentID=e({<Queue_Num={">1"}>})>} IncidentID)

For the rest, I will leave it to you.

[Vegar]

You should be able to do this usin implicit set analysis. It could look something  like this.

No of all incidents with queue_num not larger than 1

Count({< IncidentId=E({1<Queue_num={">1"}>}) >} DISTINCT IncidentId)

[shayd]

Thank tresesco!
What is that 'e' there before Queue_Num?
Also, how or where in the set can I add extra filters?

[tresesco]

@shayd , p() and e() are called element functions in set analysis. P() includes the possible values and e() excludes the possible values. For better understanding follow the link shared by Vegar above.

[shayd]

Thanks Vegar!