1 Reply Latest reply on Aug 3, 2016 6:48 PM by swaroop.gantela

    How to count number of instances between rows?

    Allen Yoo
    Allen Yoo Aug 2, 2016 4:03 PM

      We would like to get a count of number of customers transferring in and out of a membership type.  The table below shows how a transfer is identified.  If an expiry date from one row appears in the effective date for the same customer that indicates a transfer.  How do we create calculated field to compute the number of transfer ins and outs for Membership A and Membership B.

       

           

      CUSTOMERITEM_NAMEEFFECTIVE_DATEEXPIRY_DATEDESCRIPTION
      CUSTOMER 1Membership A01-Nov-1501-Jun-16Customer 1 is transferring from Membership B to Membership A
      CUSTOMER 1Membership B01-Jun-1501-Nov-15
      CUSTOMER 2Membership A01-Sep-1501-Nov-15Customer 2 is transferring from Membership A to Membership B
      CUSTOMER 2Membership B01-Nov-1501-Jun-16
      CUSTOMER 3Membership A01-Nov-1501-Jun-16Customer 3 is transferring from Membership B to Membership A
      CUSTOMER 3Membership B01-Jun-1501-Nov-15
      • 54 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: How to count number of instances between rows?
          swaroop.gantela
          swaroop.gantela Aug 3, 2016 6:48 PM (in response to Allen Yoo)

          Allen,

           

          Please see if the attached could be a starting point for you.

           

          I think the conditional will be as you described, requiring the use of Lookup to get the comparison across rows:

          IF ATTR([Expiry Date])=LOOKUP(ATTR([Effective Date]),1)

              THEN ATTR([Customer]) + " is transferring from " + ATTR([Item Name]) + " to " + LOOKUP(ATTR([Item Name]),1)

          ELSEIF ATTR([Effective Date])=LOOKUP(ATTR([Expiry Date]),1)

              THEN ATTR([Customer]) + " is transferring from "+ LOOKUP(ATTR([Item Name]),1) + " to " + ATTR([Item Name])

          ELSEIF ATTR([Expiry Date])=LOOKUP(ATTR([Effective Date]),1) THEN "Transfer A to B"

          END

           

          I think I may have gone about the counts in too round-about a way:

          First I made [SwitchInForCount]:

          IF ATTR([Expiry Date])=LOOKUP(ATTR([Effective Date]),1)

              THEN "Switch in "  + ATTR([Item Name])

          ELSEIF ATTR([Effective Date])=LOOKUP(ATTR([Expiry Date]),1)

              THEN "Switch in " + LOOKUP(ATTR([Item Name]),1)

          END

           

          Then to aggregate and show:

          IF FIRST()=0 THEN

          WINDOW_COUNT([SwitchInForCount])

          END

           

          212367switch.png