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.

[mkelemen]

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

[sunny_talwar]

That is true

Thanks for pointing that out.

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

[uridiumabs]

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.

[sunny_talwar]

Try this:

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

[uridiumabs]

Hi,

When field1 or field2 is zero is not counting.

[mkelemen]

Can you copy paste the exact definition of the variable?

[mkelemen]

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)';

[uridiumabs]

Matus, can you please type the final sentence?

[mkelemen]

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

[eduardo_dimperio]

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