Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try:
count( DISTINCT
{<Year={$(varCurrentYear)}, Week={$(varCurrentWeek)}
,CLI_ID={"=sum($(varFatturato))> $(varFascia1)"}>} CLI_ID)
Try:
count( DISTINCT
{<Year={$(varCurrentYear)}, Week={$(varCurrentWeek)}
,CLI_ID={"=sum($(varFatturato))> $(varFascia1)"}>} CLI_ID)
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
=SUM(IF(AGGR(SUM({<Year={$(varCurrentYear)}, Week={$(varCurrentWeek)}>}$( varFatturato)), CLI_ID)< $(varFascia1),1,0))
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.
Thank you so much for the help. I tried and gives me the correct result.
Thank you so much for the help. I also tried this solution and it works. Great the attached document.
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.
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)
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