Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 |
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])
What ID Segmento are getting included here?
it´s the "id" of each row
but how did you get 4 for 787? I am having difficulty in understanding how you got that number.
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 |
882 - 0...It is not? ---> 882 - 1
882 | 6641 | SILENCE | 53.06 | 22 | 66 |
No, because it has no MUSIC segment between 5 and 20
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])
You are the GUY!
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]
)
)