topic Re: Re: Get total by 1st dim when using calc dim and rank in QlikView

Get total by 1st dim when using calc dim and rank

Fri, 27 Jun 2014 11:48:54 GMT

Hello Community,

Please find attached a sample qvw.

I'd like to get the total number of employees within country.

The usual count(distinct total <Country> Emp) gives same result as count(distinct Emp).

I tried using a sort of sum(aggr(count())) but it's giving correct result on one line, the 2nd is 0.

How to get it correctly on all lines ?

Thx.

Gysbert_Wassenaar — Fri, 27 Jun 2014 12:02:29 GMT

Maybe sum(aggr(count(DISTINCT Emp),Country,Reason))

Fri, 27 Jun 2014 12:08:27 GMT

Hi Gysbert,

It's giving same value as the first expression, which is the total employees associated with the top 2 reasons displayed.

I want to get the total by country which is 4 for C1, 4 for C2, 6 for C2.

Thx 4 your suggestion.

Gysbert_Wassenaar — Fri, 27 Jun 2014 12:46:40 GMT

See attached qvw.

Fri, 27 Jun 2014 13:28:55 GMT

Awesome !

I just added a case with dimensionality() to get the total employees if I check partial sum on country.

if(dimensionality() = 0, count(total distinct Emp), your expression)

Many thx for your help Gysbert.