topic Re: Customer count by month in QlikView

Customer count by month

Sat, 20 Oct 2012 13:19:50 GMT

Hi,

I'm trying to create a bar chart showing the number of active customers for the current month and 6 months back. Customers have a StartDate and a EndDate. How to configure the date range as a dimension is what I'm struggling with. Any pointers or hints would be much appreciated!

Thanks!

Re: Customer count by month

Jason_Michaelides — Sat, 20 Oct 2012 13:31:48 GMT

So you have Date as a field as well as StartDate and EndDate?

Re: Customer count by month

Sun, 21 Oct 2012 10:20:00 GMT

I belive I got it working with help from this post: http://www.qlikcommunity.com/thread/56715

Added this in my load script:

AddMonths(MonthStart(StartDate),iterno()-1) as ContractMonth

While MonthStart(EndDate) >= AddMonths(MonthStart(StartDate),iterno()-1);

Added a car chart with ContractMonth as dimension and the following expression:

Count({<Company = {'24'}, Status = {A}>} CustomerNo)

The active contracts are listed sorted by month as per 1st of the given month, however some contracts have fictitious dates set to a year we will all be gone. So one of the month groups displayed is for active contract in the year 2099.

I got help on how to filter this in this thread: http://community.qlik.com/message/272886#272886

Message was edited by: tfolgero