topic Re: Count Active Customers in QlikView

Count Active Customers

Thu, 10 Jul 2014 11:41:40 GMT

Hi All,

A very quick question. I have a client master list with client numbers (which is incomplete) and a database with a list of client transactions. I need to count the number of active clients that I have on board. Active clients is defined as all clients on the client master list + any clients in the transaction database that has sales in the year 2014, but are not in the master client list. I have attached some dummy data. (formula in a simple text box will be ok)

Any assistance appreciated.

Thank you

Re: Count Active Customers

ali_hijazi — Thu, 10 Jul 2014 11:49:57 GMT

in your script get the clients in the transactions table that are not in your master list using where not exists

then concatenate the result to the master list of the clients

Re: Count Active Customers

datanibbler — Thu, 10 Jul 2014 11:50:20 GMT

Hi,

just do a FULL/ OUTER/ WHATEVER JOIN (it's OUTER in QV, judging from the help) between the two tables, using your Customer_ID as join_field. That should return a complete list of all customers that are in either of the tables.

Re: Count Active Customers

Thu, 10 Jul 2014 12:36:53 GMT

Hi Try it

Customer:

LOAD CID,

null() as Tran_Date

FROM

(ooxml, embedded labels, table is Customer);

Cust_Tran:

LOAD CID,

Tran_Date,

Year(Tran_Date) as Year

FROM

(ooxml, embedded labels, table is Cust_Tran)

where Year(Tran_Date)=2014;

Re: Count Active Customers

DavidFoster1 — Thu, 10 Jul 2014 12:43:04 GMT

Yet another alternative for you to consider.

1) Create a mapping table of distinct customers from your transaction table with the second column being 1.

2) On your customers table use an applymap with a default of 0.

This will give you a 1/0 flag field for active customers.

Re: Count Active Customers

Fri, 11 Jul 2014 17:09:48 GMT

If you can assign your master list clients [Transaction Date] as 0 in load script, then just

Count({<Year={2014,0}>} distinct [Client No])

That is all because NULL handling is a real mess in Qlikview... (Tell me somebody, why it is so?)

Re: Count Active Customers

Mon, 14 Jul 2014 08:53:49 GMT

By the way, can you post your sample xlsx file as well?

Re: Re: Count Active Customers

Mon, 14 Jul 2014 10:07:38 GMT

Thanks Alexander,

Please find attached

Re: Re: Re: Count Active Customers

Mon, 14 Jul 2014 10:48:47 GMT

Like that probably...

Re: Re: Re: Count Active Customers

Tue, 15 Jul 2014 06:43:23 GMT

If your problem is solved, please close the topic and choose the correct solution.

Re: Re: Re: Count Active Customers

Tue, 15 Jul 2014 17:57:37 GMT

Many thanks, much appreciated, this is perfect