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

    New Monthly Customer

    Matt N

      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 DateCurrent Month
      CustomerDATE(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))
      1-3/13/20153/1/2015
      2-3/13/20153/1/2015
      3-3/13/20153/1/2015
      4-3/12/20153/1/2015
      5-3/11/20153/1/2015
      6-3/11/20153/1/2015
      7-3/10/20153/1/2015
      8-3/6/20153/1/2015
      9-3/6/20153/1/2015
      10-3/6/20153/1/2015
      11-3/2/20153/1/2015
      122/27/20152/27/20153/1/2015
      132/27/20152/27/20153/1/2015