Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to get a count of the # of events with the same member ID that occurred on the same day.
What is the easiest way to do that?
The data I have is a list of records of the day with each record having a member ID. The member ID can repeat over the day and I just want a count of when this is repeated but it should be on the same day.
Please help.
Thanks,
Amit
Hi Amit,
it sounds like you need a nested aggregation of some sort, using the function AGGR(). So, if you just need to know the distinct count of IDs that had more than one occurrence on the same day, multiplied by the number of days with duplicate occurrences, then something like this would work:
SUM
AGGR(
IF(count(MemberID) > 1, 1),
MemberID, Date)
)
Come and learn Advanced Aggregation and Advanced Set Analysis at the Masters Summit for Qlik - coming this April to Milan, Italy!
cheers,
Oleg Troyansky