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

# 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]
)
)
```
• ###### Counting children based on a aggregate of children data for parent

I don't think that QV knows an answer to something like

if (aggr(count(..),Parent) > 0, ..)

since aggr will not necessarily return only one value. So the comparison larger than zero may not be unambigouus.

I am not really sure what you are trying to achieve. Anyway, I would start off with small pieces of code and build the total expression bottom up.

Not sure this is of any help,

Stefan

• ###### Re: Counting children based on a aggregate of children data for parent

QlikView definitely knows the answer, as I have used similar expressions successfully in the past.  I am trying to build it piece by piece, but I have been stumped for days.  Thank you for your help.