topic Re: Creating a variable to count customers spending over a certain amount in QlikView

Creating a variable to count customers spending over a certain amount

Anonymous — Mon, 12 May 2014 08:40:07 GMT

Last week I asked a question where I wanted to count customers spending more than £500 who were buying specific products. Someone gave me the answer to that particular question with this bit of code: count({<location_id={"=Sum(customer_gross_sales)>500"}>}location_id)

I then want to be able to say what percentage of customers spending more than £500 are buying each product. My question is how to count all of the customers and not just those related to the particular product? Could I establish a variable to count them and then use this?

Re: Creating a variable to count customers spending over a certain amount

anbu1984 — Mon, 12 May 2014 08:44:44 GMT

count({<location_id={"=Sum(customer_gross_sales)>500"}>}location_id) / count({1}location_id)

Re: Creating a variable to count customers spending over a certain amount

Mon, 12 May 2014 08:48:54 GMT

Hi Peter,

It seems you would like to count how many customers who has having sales more than 500 for each Location, You can achieve using this Sum(Aggr(IF(count({<location_id={"=Sum(customer_gross_sales)>500"}>}location_id))>1,1),[location_id]))';

Regards,

Chinni Kanna

Re: Creating a variable to count customers spending over a certain amount

Anonymous — Mon, 12 May 2014 08:52:02 GMT

Each customer has a distinct location_ID so customer and location_ID are synonymous.

Re: Creating a variable to count customers spending over a certain amount

Anonymous — Mon, 12 May 2014 08:52:43 GMT

Hi Anbu,

thanks for your reply. What does {1} do when put in front of location_ID?

Thanks,

Peter

Re: Creating a variable to count customers spending over a certain amount

anbu1984 — Mon, 12 May 2014 08:56:32 GMT

In set analysis, 1 Represents the full set of all the records in the application

Count({1} location_ID) gives the count of all customers in your application

Re: Creating a variable to count customers spending over a certain amount

Anonymous — Mon, 12 May 2014 09:01:30 GMT

Thanks. I'm pretty sure that's the answer then. Thanks for your help.

Re: Creating a variable to count customers spending over a certain amount

Mon, 12 May 2014 09:06:43 GMT

try this then

Sum(Aggr(IF(Sum(customer_gross_sales)>500,1),[Cust ID]))

Regards,

Chinni Kanna

Re: Creating a variable to count customers spending over a certain amount

Anonymous — Mon, 12 May 2014 09:15:15 GMT

That didn't actually seem to work as it gave a different number for each product when the number of customers spending more than £500 is the same for each product (although they don't buy all products).

Re: Creating a variable to count customers spending over a certain amount

Mon, 12 May 2014 12:05:25 GMT

Hi Peter,

Please send us sample data

Thanks & Regards,

Chinni Kanna