12 Replies Latest reply: Jan 30, 2014 12:23 PM by Manuel Raggi

# How can I count the customers who have a turnover greater than or less than a certain valu

Hello to all.

How can I count the customers who have a turnover greater than or less than a certain value.

I have defined a variable that contains the value of turnover
\$ (varFascia1) = 500

How do you time variables:
\$ (varCurrentYear) = 2014
\$ (varCurrentWeek) = 4 (is the number of the current Week)

Turnover variable: sum (\$(varFatturato))
in \$(varFatturato) contains the field for the calculation of turnover (for example NETTO)

My formula to count the customers of the working period is as follows:

count(DISTINCT{<Year={\$(varCurrentYear)}, Week={\$(varCurrentWeek)}>} CLI_ID)
(CLI_ID is the client code)

I have to add to the formula the following condition:

sum(\$(varFatturato))< \$(varFascia1)

Should I Count the number of customers that have turnover < 500 (\$ (varFascia1))

I can not find the correct syntax ..

Thanks for everything.

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Try:

count( DISTINCT

{<Year={\$(varCurrentYear)}, Week={\$(varCurrentWeek)}

,CLI_ID={"=sum(\$(varFatturato))> \$(varFascia1)"}>} CLI_ID)

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Thank you so much for the help. I tried and gives me the correct result.

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Manuel,

COUNT(DISTINCT {\$ <CLI_ID={"=sum({<Year={\$(varCurrentYear)}, Week={\$(varCurrentWeek)}>}  [\$(varFatturato)])< \$(varFascia1)"} >} [CLI_ID])

http://community.qlik.com/docs/DOC-4951

Fabrice

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

response is the correct one. I forgot to place the Period selections inside the calculation and you definitely need to do that in order to get the right number.

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

>varFascia1 and <= varFascia2

count( DISTINCT

{<Year={\$(varCurrentYear)}, Week={\$(varCurrentWeek)}

,CLI_ID={"=sum(\$(varFatturato))> \$(varFascia1)"}>} CLI_ID)

Thanks for everything.

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Mmmm...

I don't have access to my computer right now but you could try:

count( DISTINCT

{<Year={\$(varCurrentYear)}, Week={\$(varCurrentWeek)}

,CLI_ID={"=sum(\$(varFatturato))> \$(varFascia1)"}*{"=sum(\$(varFatturato))<= \$(varFascia2)"}>} CLI_ID)

Perhaps there is an easier method... but that should work.

Perhaps...

count( DISTINCT

{<Year={\$(varCurrentYear)}, Week={\$(varCurrentWeek)}

,CLI_ID={"=sum(\$(varFatturato))> \$(varFascia1) and sum(\$(varFatturato))<= \$(varFascia2)"}>} CLI_ID)

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Manuel,

You can try:

1) { [.....], CLI_ID={"=   xxxx < sum ()   < yyyy"} >} CLI_ID   [...]

2) we will sub set the second to the first one: in other words we compute the first set (sum < yyyy) and we remove from this set the second one ( sum < xxx because we want to keep only those > xxxx)

{ [.....], CLI_ID={"=  sum ()   < yyyy"} - {"=  sum ()   < xxxx "} >} CLI_ID   [...]

I am not sure about 1st solution
Fabrice

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

OK

Thank you for everything ..

Bye

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Hello Fabrice,

I tried the solution 2 works perfectly.

thanks

Manuel

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Fantastic ...!

Both solutions work.

Thank you so much aid.

Bye

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

Thank you so much for the help. I also tried this solution and it works. Great the attached document.

• ###### Re: How can I count the customers who have a turnover greater than or less than a certain valu

=SUM(IF(AGGR(SUM({<Year={\$(varCurrentYear)}, Week={\$(varCurrentWeek)}>}\$( varFatturato)), CLI_ID)< \$(varFascia1),1,0))