Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

peter_thornton
New Contributor III

Counting customers with spend between certain values

I'm trying to build a table which counts the number of customers spending in pre specified bands. Someone had asked a similar question a couple of years ago and got an answer which I can't make work for some reason.

In our data customers are location_id, sales are customer_gross_sales.

For my first spend segment I've written the following piece of set analysis:

=count({$<location_id={"sum(customer_gross_sales)>0"}*{"=sum(customer_gross_sales)<1000"}>} distinct location_id)

For the dimension I've put yr_date (which brings up Jan_2014 etc.

No data has populated my table

Is my formula to count customers wrong, or should I be using a different dimension?

1 Solution

Accepted Solutions

Re: Counting customers with spend between certain values

Perhaps like this:

=count({$<location_id={"=sum(customer_gross_sales)>0 and sum(customer_gross_sales)<1000"}>} distinct location_id)


talk is cheap, supply exceeds demand
4 Replies

Re: Counting customers with spend between certain values

Perhaps like this:

=count({$<location_id={"=sum(customer_gross_sales)>0 and sum(customer_gross_sales)<1000"}>} distinct location_id)


talk is cheap, supply exceeds demand
peter_thornton
New Contributor III

Re: Counting customers with spend between certain values

Thanks Gysbert, that worked. I hadn't realised you could use "and" in set analysis and thought you were restricted to +,-,* etc.

peter_thornton
New Contributor III

Re: Counting customers with spend between certain values

Gysbert, if I wanted to modify this fomula to count customers of a certain type only how would I do this? We have DDs and IPs and there is a table which ties this to location_ID

Would I do something like this:

=count({$<location_id={"=sum(customer_gross_sales)>0 and sum(customer_gross_sales)<1000"}, DD_IP ="IP" >} distinct location_id)


?

peter_thornton
New Contributor III

Re: Counting customers with spend between certain values

Ignore that last one I managed to work it out (I think).

=count({$<location_id={"=sum(customer_gross_sales)>0 and sum(customer_gross_sales)<1000"}, DD_IP= {'IP'} >} distinct location_id)

Community Browser