topic Distinct Count With Agrr in QlikView

Distinct Count With Agrr

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

I Have A data from 2 Tables and I want to use the data on a pivot chart where I Summarize it as Show in attached sheet Desired results. with the source data being sales details and Miner details.

1st requirement is to have distinct count of MinersName being affected by the Dimension = Count(DISTINCT MinersName)

2nd requirement is to have the distinct count of MinerName not being affected by Dimension Branch so that in the event where they are no sales to a branch we can still have those miners accounted for the other Dimension Which is manager without it returning a Null Value I have tried to use Count( Aggr(NODISTINCT DISTINCT Only(MinersName), MinersName ,Manager)) but I an not getting desired results. its counting Miners with one record twice and also those without sales are having a Zero value

Re: Distinct Count With Agrr

sunny_talwar — Thu, 09 Aug 2018 11:15:48 GMT

Try this

Aggr(NODISTINCT Count(DISTINCT [Miner name]), Manager)

Re: Distinct Count With Agrr

ngrunoz — Thu, 09 Aug 2018 12:05:17 GMT

Hi Sunny is it possible to get 1 for each of the record on

Aggr(NODISTINCT Count(DISTINCT [Miner name]), Manager)

instead of the subtotal and then have the correct subtotal so that in the event that I want to calculate a % and on my Selection i select a number of miners then i will be able to get a correct % only for the selected miners not only based on the whole manager group.

Re: Distinct Count With Agrr

sunny_talwar — Thu, 09 Aug 2018 12:11:29 GMT

Try this

If(Dimensionality() = 2,

Aggr(NODISTINCT Count(DISTINCT [Miner name]), [Miner name]),

If(Dimensionality() = 1,

Aggr(NODISTINCT Count(DISTINCT [Miner name]), Manager),

Count(TOTAL DISTINCT [Miner name])))

Re: Distinct Count With Agrr

ngrunoz — Thu, 09 Aug 2018 12:55:33 GMT

Thanks Sunny looking forward to learn much more from you.

Re: Distinct Count With Agrr

ngrunoz — Thu, 09 Aug 2018 14:24:37 GMT

Hi Sunny. I had Over looked on the SubTotal for the Branches. I would not want it to be a distinct value but to be a Sum of the distinct Value for each Branch as per Attached File.

Re: Distinct Count With Agrr

sunny_talwar — Thu, 09 Aug 2018 15:10:12 GMT

Pivot table is acting out weird when I try to change the expressions to accomplish this... this might be a bug or intended behavior... but not really sure if it can be done...

Re: Distinct Count With Agrr

ngrunoz — Fri, 10 Aug 2018 06:07:29 GMT

Thanks