topic Re: Aggr count distinct in QlikView

Aggr count distinct

curiousfellow — Thu, 08 Apr 2021 13:16:03 GMT

Please have a look at the simplified example of my qvw.

An employee works for two departments. I have to count the distinct months this employee works.

I wonder why the result is presented at just one department. I need the result for both of the departments

We are using version april 2019

Re: Aggr count distinct

rubenmarin — Sun, 11 Apr 2021 10:21:36 GMT

Hi, to count each dept you need to add dept as another dimension for the aggr:

=aggr(count (distinct month),employee,dept)

Which at the end is the same than just: count(DISTINCT month) // But maybe in the not-simplified version of the qvw makes sense to use the aggr

Re: Aggr count distinct

curiousfellow — Mon, 12 Apr 2021 13:14:50 GMT

When I use : =aggr(count (distinct month),employee,dept) the result for dept A = 6 and for dept B = 4

For both dept's the result should be 6 this employee works in 6 several months

Re: Aggr count distinct

MayilVahanan — Mon, 12 Apr 2021 13:32:02 GMT

Hi @curiousfellow

May be, try like below

=Count(TOTAL<employee> DISTINCT month)

Re: Aggr count distinct

curiousfellow — Tue, 13 Apr 2021 06:03:15 GMT

I tried that one before. When dept is collapsed the result are correct, but when I expand dept A (employee gets visible) than B shows 4 months as a result. When both dept's are epandend the result is ok.

When I expand dept B and A = collapsed B shows 4 as a result.

Re: Aggr count distinct

MayilVahanan — Tue, 13 Apr 2021 07:06:40 GMT

Might be, try like below

=Sum(Total Aggr(Count( DISTINCT month), employee))

Re: Aggr count distinct

curiousfellow — Tue, 13 Apr 2021 13:58:42 GMT

Seems to work, thank you very much.