topic Counting active customers in QlikView

Counting active customers

ulyxess1983 — Wed, 18 May 2011 12:05:58 GMT

HI guys,

Got stuck with such task - need to count active customers (that have ordered goods) and show as part of all possible customers (that's the problem).

Tried to Count(Total Customer), but it isn't showing only total of active customers,

Tried Count(All Customers) - it's showing just all customers not regarding current selection (group of customers).

Any ideas?

Thanks in advance.

Regards,

Mikhail

Counting active customers

ToniKautto — Wed, 18 May 2011 12:18:42 GMT

Not really sure what you are attmepting to do, so pleae provide a sample QVW if possibel to make evaluation easier.

How ever, if you have a active selection in your app then Count() will only count on the currently selected data set.

Re: Counting active customers

ulyxess1983 — Wed, 18 May 2011 12:29:06 GMT

Hi Toni,

Thanks for reply. here's a file

Counting active customers

ulyxess1983 — Wed, 18 May 2011 13:04:20 GMT

I've found Cunt ({1$} Customer) formula - but it's showing all data, not current selection.

Counting active customers

ToniKautto — Wed, 18 May 2011 13:47:30 GMT

Your data model contains several synthetic keys. All tables are also not linked to each other, leading to the model having two separated sets of tables. See Table Viewer (Ctrl+T) for an overview of the current model. This is not a problem by default, but very likely to cause logical problems in your application, hence you should try to eliminate the synthetic keys, and posibly create a model where all tables are related to each other.

If you have two dimensions in your chart, and an expresison containing Count(). QlikView will find the path through your datat model to cover the fields included in dimension and expression. If there is no logical relation between these fileds and field values QlikView will combine all datat to cover all possible combinations, this is the datat set used when the expressions are calculated. Count() on a field will genrated the number of hits within this set.

In case you do not want to count on the current set, then you use Set Analysis to manipulate or genrate a set to run your expression on. The manual (F1 in Desktop Client) has a very good section on how Set Analysis works.

There is a logical dilemma in your current expression. First you adapt the set by setting LevelName_1 to no selected values. Then you use TOTAL making the set expression obsolete since you have now decided to count over the data set.

In conclusion, please work on your data model to eliminate synthetic keys and to link all tables. Strive for a star och snowflake schema to avoid logical gaps in your data path. Start working on chart objects when your datat model is solid.

Re: Counting active customers

ulyxess1983 — Wed, 18 May 2011 14:25:39 GMT

Ok, Toni. I'll try to work it out.

But I need to solve this task - how to calculate all customers of current selection?

Example:

Chosen - Group 1, Product 1

Need to show - Active Customers 20, All Customers 100

Regards,

Mikhail

Counting active customers

ToniKautto — Thu, 19 May 2011 06:59:52 GMT

I have not figured out exactly how your data is set up, but Count(DISTINCT CustomerName) should give you the unique number of customer in your current selection. Compared to Count(CustomerName) that will return the number of customers in the current selection.

If you have 10 rows in your current selection that contains 5 different customers which have two rows each;

Count(DISTINCT CustomerName) will return 5

Count(CustomerName) will return 10

If your total data contains 20 rows with 10 different customers;

Count(TOTAL DISTINCT CustomerName) will return 10

Count(TOTAL CustomerName) will return 20

Notice that you should not use count on a keyfield, as QlikView will not know within which table to make the count. So if you have a customer id as a key field it is therefore better to count on the customer name as this should also be unique to the customer.

If this cleared your question, please mark the thread as solved.