5 Replies Latest reply on Sep 14, 2017 11:33 AM by Mia Lee

    Calculate/Filter any customers that have X number of "points per day" with in a given day supply

    Ty Stackhouse

      Good Morning!


      I am trying to find a way to designate or filter on customers that meet a certain points threshold within a certain amount of time.

      On the attached workbook I have made a calculation named "Points per day." I would like to see if any customer reaches or goes over 10 points with in period.  I need to see if any of my customers are going over a points threshold of 10 for any given day based on their "Day Supply"  number.


      For example on the attached "Example Karen".  On April 19th her points would be 4. On the 20th her points per day would jump to 14, (she has purchased 2 items worth 5 points each). Karen's points would remain at 14 until May 10th (the day supply for item Avery493 runs out) at which time her points would fall to 10.


      An example of where I would not want to flag a customer is "Example Maxwell" attached below. Maxwell purchased two items for a total of 8 points on 3/13/2009 and another item on 12/13/2009. The day supply for the first two items were 21 and 30 respectively. The day supply would have run out by the time the customer purchased the third item.


      The tricky part for me seems to be finding a formula that can recognize the day supply and compound points of all of the items that fall on any day while the item's "Day Supply" is still in effect. If that isn't too confusing.


      Any help/suggestions would be much appreciated!