Aggr count distinct

[curiousfellow]

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

[rubenmarin]

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

[curiousfellow]

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

[MayilVahanan]

Hi @curiousfellow 

May be, try like below

=Count(TOTAL<employee> DISTINCT month)

[curiousfellow]

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.

[MayilVahanan · Accepted solution]

Hi

Might be, try like below

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

[curiousfellow]

Seems to work, thank you very much.