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

# How to count number of instances between rows?

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.

 CUSTOMER ITEM_NAME EFFECTIVE_DATE EXPIRY_DATE DESCRIPTION CUSTOMER 1 Membership A 01-Nov-15 01-Jun-16 Customer 1 is transferring from Membership B to Membership A CUSTOMER 1 Membership B 01-Jun-15 01-Nov-15 CUSTOMER 2 Membership A 01-Sep-15 01-Nov-15 Customer 2 is transferring from Membership A to Membership B CUSTOMER 2 Membership B 01-Nov-15 01-Jun-16 CUSTOMER 3 Membership A 01-Nov-15 01-Jun-16 Customer 3 is transferring from Membership B to Membership A CUSTOMER 3 Membership B 01-Jun-15 01-Nov-15
• ###### 1. Re: How to count number of instances between rows?

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:

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