Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
)
)
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
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.