1 Reply Latest reply: Jan 25, 2016 10:12 AM by Oleg Troyansky RSS

    Counts for events with same member ID on the same day

    Amit Deepchand

      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

        • Re: Counts for events with same member ID on the same day
          Oleg Troyansky

          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