Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
klaus_feldam
Creator II
Creator II

Calculate # of active and lost records in the past 6 months (rolling period)

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.

0 Replies