topic Re: Case for aggr function? in QlikView

Case for aggr function?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi friends,

I am trying to count distinct number of events per year based on a date, but there is a problem when it is used the regular count ( Distinct "dimension") in expression chart.

Below is the fact table:

ID	Date	Event	Control
A	01/01/2015	X	1
A	01/01/2015	X	2
A	01/01/2015	X	3
A	03/07/2015	X	1
A	03/07/2015	X	2
B	02/01/2015	Z	1
B	02/01/2015	Z	2
C	02/10/2015	X	1
B	10/10/2015	X	1
B	10/10/2015	X	2

So, in this case, when I use the expression Count ( distinct ID), having the dimension year(Date) and Event, the result is:

A = 1

B = 1

C = 1

But the desirable result is:

A = 2

B = 2

C = 1

Does anyone have an idea of what can be done?

Thanks in advance.

Re: Case for aggr function?

Gysbert_Wassenaar — Wed, 19 Sep 2018 14:11:20 GMT

Try count(distinct Date) as expression with ID as dimension

Re: Case for aggr function?

Frank_Hartmann — Wed, 19 Sep 2018 14:11:33 GMT

Straight Table:

Dim = ID

Exp1 = count(aggr( ID,Date))

Re: Case for aggr function?

Anonymous — Wed, 19 Sep 2018 14:40:56 GMT

Thanks friends,

I forgot to include that the result will be shown in a chart like this: