3 Replies Latest reply on Jul 16, 2018 4:33 PM by Wesley Magee

    Count Number of Children

    jason.hodge

      A have a dataset full of parent data and children.  Each row is a new entry, and it is either a parent, or a child.  Example:

       

      ProjectRow IDParent ID
      Child IDs
      A1null2,3
      B21null
      C31null
      D4nullnull

       

      As you can see, projects B and C are children of project A.  Project D is another parent project.  Row ID is what is used to reference the different projects, and Parent ID references the Row ID of the Parent.  What I'm trying to do is count the number of children that each parent project has.  I'm attempting to do this through a calculated field, but am stumped.  What I'd like to see is another measure showing the number of children for each parent.  In the example above, it would be {2,null,null,0}.

       

      Info that may or may not be useful:

      1. Not all parents necessarily have a child

      2. Children are always located in the rows immediately below the parent

      3. Data comes from a Smartsheet Connector, so pivoting isn't really an option.

      4. This can be done by counting the child IDs (I've done this), but what that misses is any reference to the rows themselves.  What I'd like to be able to do down the road is add another measure for whether or not a project is complete, and then calculate a % complete for the parent based on the completion rate of its children.

        • 1. Re: Count Number of Children
          Wesley Magee

          Jason,

          Could you split the Child IDs column and then add them together? The calc might look like:

           

          COUNT(Child ID - Split 1) + COUNT(Child ID - Split 2) + COUNT(Child ID - Split 3) + COUNT(Child ID - Split 4)

           

          I don't know how many potential children would be involved. If there were a lot, then this might not be a good solution.

          -Wesley

          • 2. Re: Count Number of Children
            jason.hodge

            Wesley-

             

            Yes, that would work and I've done it that way.  What I'd like to do though (and I probably should have said this more clearly) is to have another measure called 'Complete?' that's a yes or no.  Then I'd take these yes or no's and determine a percent completion of each project based on the completion of the children.  Hopefully this makes sense.  What counting the children column misses is a way to actually search for data along each child row; it knows how many children there are but it doesn't know where they are in the table.  Now if there was a way that I could count the entries in that column (I've alreaady figured that out) and then examine that many rows under the parent for complete yes/no, then that would work as the children are always directly under the parent.

            • 3. Re: Count Number of Children
              Wesley Magee

              If the table is setup as displayed, then you should be able to put this new calc into the view along with the field for Row ID to get a count per Row ID.

               

              Did I miss something in the setup?