Problems with set analysis

[uridiumabs]

Hello,

I need to show a column in a Pivot Table wich counts some rows depending on a condition:

the formula is:  Count(Distinct  {$<[$(variable1)+$(variable2)]= {'>0'}>} [CommercialSite])

So I want to count comercial sites that variable1 greater than 0 and variable2 greater than 0. This sentence is not working because is counting all rows as if no condition is set.

I have checked the syntax and for me is ok, Can anybody help me with this?

Thank you very much.

[rittermd]

I am thinking that you might want to break up the set analysis into 2 parts.

Variable1>0,Variable2>0

Not the correct syntax.  But just separate them as if 2 different expressions.

[mkelemen]

Hi Mark,

what you are suggesting is not the same. For example your suggestion does not work for v1=2, v2=-1.

Edit: the text says what v1>0 and v2>0 though - so it is ok

  Matus

[sunny_talwar]

May be this:

Count(DISTINCT {$<CommercialSite = {"=$(variable1) > 0 and $(variable2)>0"}>} [CommercialSite])

[eduardo_dimperio]

Hi Juan,

I was reading about, and i believe that when you use (variable1)+$(variable2), that creates a single group so if your have value >0 in variable1 OR variable2, that increase your count.

I'll figure out how solve, but the problem is basically that, as long i had read

[uridiumabs]

Hi,

I've read all your answers, and changed the sentence in this way:

Count(Distinct {$<[$(variable1)]= {'>0'}, [$(variable2)]= {'>0'}>}  [CommercialSite])

Let me clarify, I just want to count the commercial sites that variable1 is greater than zero and variable2 is greater than zero, imagin that variable1 and variable2 are sales amount and whant to count comercial sites thas have sales.

The output is the same, I get the sum off all commercial sites no matter if they have sales or not.

What I see is that if I use the fields of the table instead variables ths sentence is working OK.

So i guess the problem is not in the sentence but in the use of variables but I've read that using variables is alowed in set analysis.

Any idea?

[mkelemen]

Hi,

I tested the formula in QS 3.1.4 and it gives me correct result.

How do you define the variables?

  Matus

[uridiumabs]

Hi Matus,

Variables are just defined as SUM(field).

My mistake I said variable1 and variable 2 and actually meant OR.  So I want to count the commercial sites that variable1 is greater than zero OR variable2 is greater than zero.

Is that posssible in set analysis.

Thanks a lot.

[mkelemen]

The important part is that the formula holds Sum() function and not a field name.

The formula you posted above works only if you have field names in the variables.

Take the formula from Sunny T and Change 'and' to 'or'.

Count(DISTINCT {$<CommercialSite = {"=$(variable1) > 0 or $(variable2)>0"}>} [CommercialSite])

that works with Sum() inside.

[sunny_talwar]

Or this:

Count(DISTINCT {$<CommercialSite = {"=RangeSum($(variable1), $(variable2)) > 0"}>} [CommercialSite])