0 Replies Latest reply: Mar 16, 2015 12:31 PM by Matt N

# New Monthly Customer

Hello,

I was looking for help expanding my current months formula to be more dynamic. The problem with my existing logic is it will not Show past months without updating hardcoding -1, -2 and so on. If a user clicks on February vs January I want to show the new customers in the selected Month.

I have successfully applied the concept to the current month.

Customer with the last invoice date less than start of this month. This gives me new customers by applying isNull in my If.

=IF(

IsNull(DATE(AGGR(MAX({1<InvoiceDate = {'<\$(=monthstart(AddMonths(Date(Today()),0)))'}>} InvoiceDate),CustomerKey) ))

AND DATE(AGGR(MIN({1<ActivityMonthYear = {'\$(=date(monthstart(AddMonths(Date(Today()),0)), 'MMM-YY'))'}>} InvoiceDate),CustomerKey) ) >= monthstart(AddMonths(Date(Today()),0))

,InvoiceMonthYear

)

 Current Month Date Current Month Customer DATE(AGGR(MAX({1} InvoiceDate),CustomerKey) ) AND DATE(AGGR(MIN({1} InvoiceDate),CustomerKey) monthstart(AddMonths(Date(Today()),0)) 1 - 3/13/2015 3/1/2015 2 - 3/13/2015 3/1/2015 3 - 3/13/2015 3/1/2015 4 - 3/12/2015 3/1/2015 5 - 3/11/2015 3/1/2015 6 - 3/11/2015 3/1/2015 7 - 3/10/2015 3/1/2015 8 - 3/6/2015 3/1/2015 9 - 3/6/2015 3/1/2015 10 - 3/6/2015 3/1/2015 11 - 3/2/2015 3/1/2015 12 2/27/2015 2/27/2015 3/1/2015 13 2/27/2015 2/27/2015 3/1/2015