Help with Set Analysis and aggregation

[Clever_Anjos]

I have a dataset like below (excel file attached too)

I need to know (aggregated on [ID Som]) how many segments have a [ID Som] that have a segment = Silence of Duration between 22  and  66 (example) and also have segments = MUSIC of duration between 5 and 20.

In this sample I should have:

• 787 - 4
• 882 - 0 (It has SILENCE but hasn´t MUSIC)

ID Som	ID Segmento	Tipo Segmento	Duration
787	5923	VOICE	23,30
787	5924	SILENCE	8,23
787	5925	VOICE	1,84
787	5926	SILENCE	4,73
787	5927	VOICE	1,65
787	5928	SILENCE	29,22
787	5929	VOICE	13,34
787	5930	SILENCE	3,68
787	5931	VOICE	16,36
787	5932	SILENCE	51,37
787	5933	MUSIC	5,43
787	5934	SILENCE	4,78
787	5935	VOICE	2,61
787	5936	SILENCE	3,47
787	5937	VOICE	11,54
787	5938	SILENCE	10,27
787	5939	VOICE	9,09
787	5940	MUSIC	9,33
787	5941	SILENCE	9,77
787	5942	VOICE	24,22
882	6640	VOICE	68,25
882	6641	SILENCE	53,06
882	6642	VOICE	7,69
882	6643	SILENCE	10,00
882	6644	VOICE	6,35
882	6645	SILENCE	8,24
882	6646	VOICE	13,62
882	6647	SILENCE	6,91
882	6648	VOICE	69,60

[sunny_talwar]

What ID Segmento are getting included here?

[Clever_Anjos]

it´s the "id" of each row

[sunny_talwar]

but how did you get 4 for 787? I am having difficulty in understanding how you got that number.

[Clever_Anjos]

It´s the "count of ok"

ID Som	ID Segmento	Tipo Segmento	Duracao Segmento	Check
787	5924	SILENCE	8,23	outside threshold < 22
787	5926	SILENCE	4,73	outside threshold < 22
787	5928	SILENCE	29,22	ok between 22 and 66
787	5930	SILENCE	3,68	outside threshold < 22
787	5932	SILENCE	51,37	ok between 22 and 66
787	5933	MUSIC	5,43	ok between 5 and 20
787	5934	SILENCE	4,78	outside threshold < 22
787	5936	SILENCE	3,47	outside threshold < 22
787	5938	SILENCE	10,27	outside threshold < 22
787	5940	MUSIC	9,33	ok between 5 and 20
787	5941	SILENCE	9,77	outside threshold < 22

[othniel2014]

882 -  0...It is not? ---> 882  - 1

882

6641

SILENCE

53.06

22

66

[Clever_Anjos]

No, because it has no MUSIC segment between 5 and 20

[sunny_talwar · Accepted solution]

May be this:

=Count(DISTINCT {<[ID Som] = {"=(Count({<[Tipo Segmento] = {'SILENCE'}, [Duracao Segmento] = {'>=22<=66'}>}[Duracao Segmento]) > 0 and

  Count({<[Tipo Segmento] = {'MUSIC'}, [Duracao Segmento] = {'>=5<=20'}>}[Duracao Segmento]) > 0)"}>*

  (<[Tipo Segmento] = {'SILENCE'}, [Duracao Segmento] = {'>=22<=66'}>+<[Tipo Segmento] = {'MUSIC'}, [Duracao Segmento] = {'>=5<=20'}>)} [ID Segmento])

[Clever_Anjos]

You are the GUY!

[Clever_Anjos]

While this I´ve reached this "ugly"  expression too

Sum(

  Aggr(

  If(

  Count(if(([Duracao Segmento]>=5 and [Duracao Segmento]<=20 and [Tipo Segmento]='MUSIC'), [ID Segmento])) > 0 and

  Count(if(([Duracao Segmento]>=22 and [Duracao Segmento]<=66 and [Tipo Segmento]='SILENCE'), [ID Segmento])) > 0 ,

  Count(if(([Duracao Segmento]>=5 and [Duracao Segmento]<=20 and [Tipo Segmento]='MUSIC'), [ID Segmento])) + Count(if(([Duracao Segmento]>=22 and [Duracao Segmento]<=66 and [Tipo Segmento]='SILENCE'), [ID Segmento])) ),

  [ID Som]

  )

)