2 Replies Latest reply: Aug 19, 2011 12:49 PM by connextions1 RSS

    Counting children based on a aggregate of children data for parent

      I am trying to count all children that belong to a parent fulfilling certain conditions.  The conditions for this parent are aggregates of data belonging to the children.  However, no one child will necessarily fulfill the criteria itself.  Since this is part of a point-in-time report, I am only counting data that existed base on the variable PointInTime.  Interactions and purchases belong to children, which belong to parents.  Activity belongs to interaction.  Any help would be appreciated, as I am stumped.

       

      Here is the expression:

       

      =count(
          DISTINCT
          {$<[Child Create Date] = {"<=$(=date(PointInTime))"}>}
          if(
              [Parent]
              = if(
                  aggr(
                      count(
                          {$<[Activity] = {'asdf1', 'asdf2', 'asdf3'},
                          [Interaction Day] = {"<$(=date(PointInTime))"}>}
                          [Interaction ID]
                      ),
                      [Parent]
                  ) > 0
                  or 
                  aggr(
                      count(
                          {$<[Purchase Creation Timestamp] = {"<$(=timestamp(PointInTime))"}>}
                          [Purchase ID]
                      ),
                      [Parent]
                  ) > 0,
                  [Parent]
              ),
              [Child ID]
          )
      )