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

[Anonymous]

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 .. Can you help me please?

Thanks for everything.

[Carlos_Reyes · Accepted solution]

Try:

count( DISTINCT

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

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

[#3]

Manuel,

Try this please:

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

I had published a dc on set analysis, it can help you:

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

Fabrice

[MK_QSL]

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

[Carlos_Reyes]

AUNEZ FABRICE 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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Thank you for your aiuto.Se instead I had to count the customers comrpesi between two variables?

>varFascia1 and <= varFascia2

count( DISTINCT

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

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

Where do I add the second condition in your formula?

Thanks for everything.

[Carlos_Reyes]

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)

[#10]

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