topic Re: If Expression - Distinct Count in QlikView

If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 14:22:22 GMT

Good Day,

I need help with the following.

I have got my expression correct of the total buying customers, my problem is that I am struggling to get the buying customers correct, what the expression must do is the following:

If "Customer Number" have 1 or more of a product bought, then only do the distinct count, else do not count the "Customer Number".

Thank you for the support!!

Kind Regards,

Jan

Re: If Expression - Distinct Count

Anil_Babu_Samineni — Mon, 08 May 2017 14:27:12 GMT

Would you provide data and expected result, please?

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 14:38:19 GMT

Hello, I have attached the documents and made some screen shots below:

So this is the data of the available products.

There is 4 customers in total that bought something and 3 is from the SSD group. As per picture below:

So the result that I am looking for needs to be the following:

Total Customer Bought: 4 (This part I am getting correct)
Productid 6541 - Total Qty (12) - 3 Customers Bought Out of 4 (75% Bought) - This is where I am going wrong, I do get 4 everytime.

Thank You!

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 14:39:58 GMT

This is my expression also, as I filter by date.

=If(GetSelectedCount(OrderDate), Count(DISTINCT [account with Prefix]),0 )

Thank You

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 14:41:23 GMT

Of My original post, of the 3 867 customers, only 520 have bought SSD. So the answer should have been 14% and not 100%

Re: If Expression - Distinct Count

prieper — Mon, 08 May 2017 14:43:36 GMT

COUNT(DISTINCT CustomerNumber) is probably too simple, would make sense to disclose, how the datamodel would look like and how you do calculate the "Total No Customers" then. Is ProFilter the product?

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 14:45:38 GMT

Good Day,

productId would be the table for all listed products that is available.

Thank You

Re: If Expression - Distinct Count

Anil_Babu_Samineni — Mon, 08 May 2017 14:46:14 GMT

Here, Can you describe more for 6541 which is result. May be this?

=If(GetSelectedCount(OrderDate) >= 1, Count(DISTINCT [account with Prefix]),0 )

Re: If Expression - Distinct Count

vishsaggi — Mon, 08 May 2017 14:49:50 GMT

Can you try like:

= If(GetSelectedCount(OrderDate) > 0, Count(DISTINCT [account with Prefix]), 0 )

Else Can you share a sample to look into?

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 15:02:39 GMT

Hello,

I have made another example of what I am trying to achieve.

As per this picture below, I am doing an Distinct count to see how many customers are linked to the warehouse, this should show every time 50, currently it only shows who bought as per the right hand side table.

So the expression should be something like this...

All customers linked to "Warehouse" get Distinct count and populate that number to each "item", then do a count of "customers" against each "item" - I hope this makes sense.

Thank You

Re: If Expression - Distinct Count

prieper — Mon, 08 May 2017 15:14:00 GMT

Then the 50 represents all customers, who bought shirts, pants etc.

SUM(TOTAL DISTINCT CustomerNumber)

should do then

Peter

edit: added DISTINCT to formula

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 15:21:01 GMT

Peter,

Thank you, I am getting there

Now, it is giving me the correct overall number of customers in the database, but when I expand it, it doesn't split it by total customers allocated to each warehouse but all together.

For example, if warehouse 2 has 100 customers allocated, then it shows also 150 customers as it combines warehouse 1 (50 customers) and warehouse 2 (100 customers) together.

How can I get it to give the total customers of each warehouse when expanding the chart?

Edit:

When expanding, if a warehouse has 50 customers allocated to it, it needs to show all 50 customers, one expression showing that this is a customer linked to the warehouse (TOTAL) does give it in a number but does not physically show the customer.

Thank You!

Re: If Expression - Distinct Count

prieper — Mon, 08 May 2017 15:27:43 GMT

Then you need to add the distinguisher as well:

COUNT(TOTAL DISTINCT <Warehouse> CustomerNumber)

This should deliver the number of customers, regardless the dimension, except for Warehouse

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 15:34:20 GMT

Hi Peter,

It still combining the total customers for both warehouses, warehouse 1 (50 Customers) plus warehouse 2 (100 customers), giving me a total for both of 150 when expanding.

Also,

When I expand, I see the number 150, but it does not show all the customers, only the ones who bought.

My idea behind this is to build a penetration report, to see how many of the customers bought certain products, and we need to be able to identify the ones that haven't bought anything by customer level

Thank You

Re: If Expression - Distinct Count

vishsaggi — Mon, 08 May 2017 15:41:31 GMT

Sometime you get quick resolutions if you can share your sample data. If it is confidential just scramble and send us a sample. How you do ?-> Preparing examples for Upload - Reduction and Data Scrambling

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 15:59:47 GMT

Thank You,

Please check screenshot below:

Products is showing the overall correct number, the total database is 3931 customers, but once you expand it, it is listing all the warehouses, each warehouse have about 50 - 80 customers allocated to each, but as per example below, it stills shows the overall amount, not the amount of customers allocated to each warehouse.

Thank You!

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 16:00:41 GMT

This is my current expression:

=Count(TOTAL DISTINCT{<OrderDate=>} [account with Prefix])

Thank You

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 16:07:51 GMT

Hello,

I have just checked, when I click on one of the wholesalers, the total is correct for that wholesaler.....

How can I get these numbers without clicking on them

Thank You

Re: If Expression - Distinct Count

goro2010 — Mon, 08 May 2017 16:12:27 GMT

Re: If Expression - Distinct Count

vishsaggi — Mon, 08 May 2017 16:38:28 GMT

What happens when you try this:

=Count(TOTAL DISTINCT{<OrderDate=, Warehouse=>} [account with Prefix])