topic New Customer Counts in QlikView

New Customer Counts

tmumaw — Wed, 16 Sep 2009 01:33:51 GMT

Need a little help counting "New" customers. Here is my scenario. We will create a customer in 2008, but the customer does not become a "true" customer until their first invoice is cut. What can I do to identify these customers once they are invoiced?

Thanks

New Customer Counts

Wed, 16 Sep 2009 01:39:21 GMT

I imagine you invoices table contains a customer ID. Once you link the two tables together, you will have customers with invoices and those without. Those without will have Nulls for the linked invoice fields. If you make a table with customers as the dimension and add Count(Invoice ID) as the expression, that would give you a list of true customers. Customers with no invoices will not appear (if Suppress Zero Values is checked).

Depending on what exactly you want to do, there may be different approaches.

New Customer Counts

tmumaw — Wed, 16 Sep 2009 01:50:14 GMT

I know who my true customers are, my problem is out of those true customers who is "new" this year. Is there a way to identify the new customers via the script? I only want to count the new customers for this year.

Thanks

New Customer Counts

Wed, 16 Sep 2009 02:00:19 GMT

You could use Set Analysis to only count those invoices for the current year.

Count({<InvoiceYear = {'$(=Year(Today()))'}>} InvoiceID)

That would only count invoices from this year. You could also use

Count({<InvoiceYear = {'<$(=Year(Today()))'}>} InvoiceID)

That would give invoices from anything prior to this year. If the second expression equals 0 and the first is greater than zero then they are new customers for this year.

If you want to do that in a load script, you could probably use similar logic. Count all the invoices for previous years and if that is zero and they have invoices this year, they are new for this year.

New Customer Counts

tmumaw — Wed, 16 Sep 2009 02:29:50 GMT

I tried it, but not what I was expecting to see. It showed no new customers which I know is not right.

New Customer Counts

tmumaw — Wed, 16 Sep 2009 02:48:58 GMT


tmumaw wrote:
I tried it, but not what I was expecting to see. It showed no new customers which I know is not right. <div></div>

It did work when I use the customer as a dim. My problem is I need to do it by cost center. What I need to do is work this logic into my load script.

Thanks

New Customer Counts

Wed, 16 Sep 2009 04:02:52 GMT

To do it in your load script, you would load the tables multiple times or use a subquery. Something like this should work:

Select *
From Customers, Invoices,
 (Select CustomerID, Count(Invoice Id) As CurInvoices
 From Customers, Invoices
 Where Customers.CustomerID = Invoices.CustomerID
 and InvoiceYear = Year(SYSDATE)
 Group By CustomerID) CY,
 (Select CustomerID, Count(Invoice Id) As PrevInvoices
 From Customers, Invoices
 Where Customers.CustomerID = Invoices.CustomerID
 and InvoiceYear < Year(SYSDATE)
 Group By CustomerID) PY
Where Customers.CustomerID = Invoices.CustomerID
 and Customers.CustomerID = CY.CustomerID
 and Customers.CustomerID = PY.CustomerID

Now that's pretty messy, but it should work. There are probably other ways to do it in QlikView as well. I usually prefer to do my work in a SQL editor and once it's working there, just paste it right into QlikView. Some people would probably rather pull the basics from the database and then QlikView to do the dirty work.

Note: This is Pl/SQL (Oracle) syntax, it's a little different if you're using SQL Server. I didn't test any of that code, so it may not be perfect. The logic is the important part.