Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ngrunoz
Contributor II
Contributor II

Distinct Count With Agrr

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

1 Solution

Accepted Solutions
sunny_talwar

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])))

Capture.PNG

View solution in original post

7 Replies
sunny_talwar

Try this

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


Capture.PNG

ngrunoz
Contributor II
Contributor II
Author

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.

sunny_talwar

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])))

Capture.PNG

ngrunoz
Contributor II
Contributor II
Author

Thanks Sunny looking forward to learn much more from you.

ngrunoz
Contributor II
Contributor II
Author

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.

sunny_talwar

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...

ngrunoz
Contributor II
Contributor II
Author

Thanks