22 Replies Latest reply: Jan 13, 2017 7:27 AM by Eduardo DImperio

# Problems with set analysis

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.

• ###### Re: Problems with set analysis

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.

• ###### Re: Problems with set analysis

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

• ###### Re: Problems with set analysis

May be this:

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

• ###### Re: Problems with set analysis

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

• ###### Re: Problems with set analysis

Hi,

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?

• ###### Re: Problems with set analysis

Hi,

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

How do you define the variables?

Matus

• ###### Re: Problems with set analysis

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.

• ###### Re: Problems with set analysis

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.

• ###### Re: Problems with set analysis

Or this:

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

• ###### Re: Problems with set analysis

Hi,

this works for the total sum > 0.

But if one of the parts is < 0 and the other is > 0 but the total sum is < 0 it does not return correct value.

Your original formula with or works also in this case.

This is probably nitpicking but it could be problem in case they have returns or credit notes.

Matus

• ###### Re: Problems with set analysis

That is true

Thanks for pointing that out.

UPDATE: Assuming there are no negative numbers in play, my expression might work

• ###### Re: Problems with set analysis

Hi,

No negative numbers, but still doesn't work. I've found a way for it to work:

Count(Distinct {\$<[FIELD1]= {'>0'} > + \$<[FIELD2]= {'>0'} >}  [CommercialSite])

But only fields, not variables.

Thanks.

• ###### Re: Problems with set analysis

Try this:

Count(Distinct {\$<CommercialSite = {"=[FIELD1] > 0 or [FIELD2] > 0"}>}  [CommercialSite])

• ###### Re: Problems with set analysis

Hi,

When field1 or field2 is zero is not counting.

• ###### Re: Problems with set analysis

Can you copy paste the exact definition of the variable?

• ###### Re: Problems with set analysis

Have a look at this - I replaced the \$ with @ in the header so that the variables are not parsed

The variables are defined as

Set variable1 = 'Sum(Sales1)';

Set variable2 = 'Sum(Sales2)';

• ###### Re: Problems with set analysis

Matus, can you please type the final sentence?

• ###### Re: Problems with set analysis

It is in the header of the 3rd column - you just need to change @ to \$.

• ###### Re: Problems with set analysis

Sorry about the question, but when i use \$ and @?

• ###### Re: Problems with set analysis

@ has no special meaning here I just used it so that the variable is visible - otherwise the \$(variable) would be replaced by the value inside.

• ###### Re: Problems with set analysis

Understood, thank you Matus

• ###### Re: Problems with set analysis

I'm sorry Matus, I don't get you.

Could you please attach the screenshot?

Thank you very much