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: 
gdham123
Contributor III
Contributor III

Count dimension within unique dimension

Hi guys,

I have a table where I am trying to count the number of distinct Child IDs are within a parent dimension. Each Child ID has to be counted once within each Parent ID, but can be counted again if it appears inside another Parent ID. Each parent has to be distinct, and each child has to be distinct within the parent ID. 

This is a small sample of the data. The correct answer for Child IDs would be 17, not 22. Parent IDs often appear many times within the larger data-set.

Child_IDPARENT_ID
QX02809923a041i00000SsqCIAAZ
QX02205503a041i00000SsqCIAAZ
QX78342143a041i00000SsqCIAAZ
QX00541243a041i00000SsqCIAAZ
QX47972193a041i00000SsqCIAAZ
QX02809923a041i00000SsqCIAAZ
QX02205503a041i00000SsqCIAAZ
QX78342143a041i00000SsqCIAAZ
QX00541243a041i00000SsqCIAAZ
QX47972193a041i00000SsqCIAAZ
QX04588863a041i00000SsqCmAAJ
QX11389383a041i00000SsqCmAAJ
QX52572863a041i00000SsqCmAAJ
QX02629263a041i00000SsqCmAAJ
QZ64178663a041i00000UMwojAAD
QZ51922313a041i00000UMwojAAD
QZ28961483a041i00000UMwojAAD
QZ00529133a041i00000UMwojAAD
QZ00137373a041i00000UMwojAAD
QZ00264053a041i00000UMwojAAD
QZ43343813a041i00000UMwojAAD
QZ24072023a041i00000UMwojAAD

 

Thanks for your assistance.

Gary

1 Solution

Accepted Solutions
sunny_talwar

How about this?

=Sum(Aggr(
    Count(DISTINCT {<CallType = {'GROUP'}>} Child_ID)
, PARENT_ID))

View solution in original post

5 Replies
sonkumamon
Creator
Creator

Try:

Sum(Aggr(Sum(Distinct Child_ID), PARENT_ID))

 

gdham123
Contributor III
Contributor III
Author

Thanks Sonkumamon, unfortunately that doesn't seem to work. I've tried 

=Count(Aggr(Count(Distinct Child_ID), PARENT_ID))

as well as variations of that, but none of them seem to work on the larger data-set.

Sorry, I should have said that there is also a Calltype that has to be 'GROUP'. So I've also tried:

=Only(Aggr(Count(Distinct {<CallType={'GROUP'}>}Child_ID),PARENT_ID))

=Count(Distinct {<CallType={'GROUP'},PARENT_ID={"=Count(Distinct PARENT_ID)"}>}Child_ID)

and variations of these, but none of them give the correct answer.

Thanks for trying.

 

 

 

sunny_talwar

How about this?

=Sum(Aggr(
    Count(DISTINCT {<CallType = {'GROUP'}>} Child_ID)
, PARENT_ID))
sonkumamon
Creator
Creator

Hi Sunny,

I would also insert the set analysis in the first expression:

=Sum( {<CallType = {'GROUP'}>} Aggr(
    Count(DISTINCT {<CallType = {'GROUP'}>} Child_ID)
, PARENT_ID))

 

Just in case.

gdham123
Contributor III
Contributor III
Author

Thanks Sunny_Talwar this is perfect. It works!!