topic Re: Aggregation on Dimension in QlikView

Aggregation on Dimension

Clever_Anjos — Tue, 23 Aug 2016 20:10:10 GMT

I have a dataset like Excel attached (please refer to Help with Set Analysis and aggregation‌‌ as requirement)

stalwar1‌ brightly helped me to find a way of creating my expression to find

(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)

Now I have to create a graph like this

how many [ID Som] have 2 segments, ow many [ID Som] have 3 segments and so on.

Any idea how to solve this?

Re: Aggregation on Dimension

sunny_talwar — Tue, 23 Aug 2016 20:23:01 GMT

Check if the attached meets your requirement?

Re: Aggregation on Dimension

sunny_talwar — Tue, 23 Aug 2016 20:27:45 GMT

Updated sample

Re: Aggregation on Dimension

Clever_Anjos — Tue, 23 Aug 2016 20:31:43 GMT

stalwar1‌, you really deserve to be at Qlik Mount Olympus

Re: Aggregation on Dimension

sunny_talwar — Tue, 23 Aug 2016 20:32:20 GMT

Where is that taking place, I am in

Re: Aggregation on Dimension

Anonymous — Tue, 23 Aug 2016 20:51:45 GMT

Could you please also post the expression in this thread, so I can also learn.

No Qlik View and can not open the attached file.

Thanks!

Re: Aggregation on Dimension

Clever_Anjos — Tue, 23 Aug 2016 21:03:14 GMT

My final expression use some variables but these expression is correct

=Aggr(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]), [ID Som])