Calling all Qlik Developer experts!
We are looking to put the finishing touches on a customer dashboard, but have two things outstanding:
Calculate # of active customers in the past 6 months (rolling period)
Calculate # of customers who have not ordered in the past 6 months (rolling period)
While it's easy for us to figure out how many active and lost customers we have had in the past 6 months, I need a bit of guidance on how best to calculate the number of new and lost customers in the past 6 months based on a rolling base date.
Example for new customer count:
If my base date is October 2014, I want to be able to calculate how many active customers we had in the prior 6 months (April 1 - September 30, 2014).
Formula used for active customers in the past 6 months (from today):
count(DISTINCT if(MonthStart(TRANDATE)>AddMonths(MonthStart([Set Start Date]),-6 ) and TRANDATE<MonthStart([Set Start Date]),CUSTOMER_ID))
Example for lost customer count:
If my base date is October 2014, I want to be able to calculate how many customers have NOT in the prior 6 months (April 1 - September 30, 2014).
Any ideas from this outstanding community would be greatly appreciated.
Thank you.
