New Monthly Customer

Mon, 16 Mar 2015 16:31:34 GMT

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 = {'<$(=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/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

topic New Monthly Customer in QlikView

New Monthly Customer