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

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!

• ###### 2. Re: Calculate/Filter any customers that have X number of "points per day" with in a given day supply

I guess that would help

• ###### 3. Re: Calculate/Filter any customers that have X number of "points per day" with in a given day supply

Hi, Ty

I have created 2 calculated fields and a parameter (date to use for a reference date) - this can be changed later for any reference date you'd like on your workbook (even a calc field, today() if you want to use today's date as a reference date)

Basically I calculated Shipdate+DateSupply date- to compare to reference date to figure out when the point expires, (DATE(dateadd('day',[Day Supply],[Ship Date])))

and Points Per Day to be zero when reference date is passed the Shipdate+datesupply. (IIF([RefDate]<=[Shipdate+DaySupply_Mia],[Points per Day],0))

Hope this helped.

Thank you-

Best,

Mia

• ###### 4. Re: Calculate/Filter any customers that have X number of "points per day" with in a given day supply

Mia Lee Thank you for the response!

Unfortunately, I was unable to do a comparable replication of "Points per Day_Mia". In my workbook my "Points per Day" field is calculated by two other fields, one of the other fields coming from another data source. In order to bring these together they must be aggregated. Therefore, when I try to calculate "Points Per Day_Mia"  I am met with a "cannot mix aggregate and non aggregate arguments with this function" error.

Thank you  for your insight into my issue I will try to find a work around for this problem.

• ###### 5. Re: Calculate/Filter any customers that have X number of "points per day" with in a given day supply

Hi, Ty

If you can't fix aggregate/non-aggregate on your own, try using a sample workbook that demonstrates all your troubles next time. Hope you find a clue from my suggestions, though.

Thank you.