Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_ID | PARENT_ID |
QX02809923 | a041i00000SsqCIAAZ |
QX02205503 | a041i00000SsqCIAAZ |
QX78342143 | a041i00000SsqCIAAZ |
QX00541243 | a041i00000SsqCIAAZ |
QX47972193 | a041i00000SsqCIAAZ |
QX02809923 | a041i00000SsqCIAAZ |
QX02205503 | a041i00000SsqCIAAZ |
QX78342143 | a041i00000SsqCIAAZ |
QX00541243 | a041i00000SsqCIAAZ |
QX47972193 | a041i00000SsqCIAAZ |
QX04588863 | a041i00000SsqCmAAJ |
QX11389383 | a041i00000SsqCmAAJ |
QX52572863 | a041i00000SsqCmAAJ |
QX02629263 | a041i00000SsqCmAAJ |
QZ64178663 | a041i00000UMwojAAD |
QZ51922313 | a041i00000UMwojAAD |
QZ28961483 | a041i00000UMwojAAD |
QZ00529133 | a041i00000UMwojAAD |
QZ00137373 | a041i00000UMwojAAD |
QZ00264053 | a041i00000UMwojAAD |
QZ43343813 | a041i00000UMwojAAD |
QZ24072023 | a041i00000UMwojAAD |
Thanks for your assistance.
Gary
How about this?
=Sum(Aggr(
Count(DISTINCT {<CallType = {'GROUP'}>} Child_ID)
, PARENT_ID))
Try:
Sum(Aggr(Sum(Distinct Child_ID), PARENT_ID))
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.
How about this?
=Sum(Aggr(
Count(DISTINCT {<CallType = {'GROUP'}>} Child_ID)
, PARENT_ID))
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.
Thanks Sunny_Talwar this is perfect. It works!!