AND/OR

[agsearle]

I have the following code in a chart straight table dimension:

=if(aggr(Count({<Month=,Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)-30))"}>} UniqueID),BP)>3
OR
aggr(sum({<Lock_Valid_To={">=$(=date(today(0)))"}>} Lock_Count),BP)>0,BP)

This code was written by a contractor, but it isn't doing what we want.

There are two criteria here. The first aggregates by BP showing accounts where the count is 4 or above, and the second whether there is a Lock on the account. We have two similar tables, the first has the code above, but 'OR' is replaced by 'AND'. So the first list shows all accounts that fit both the criteria, and works fine.

However, for the second table we only want to show accounts that meet either criteria, not both. The 'OR' statement shows not only those that have one or other criteria but also those with both, which we already have in the first list. Is there a way to prevent showing the ones from the first table in the second?

Thought it best to try and get a solution here first, before we go back to the contractor.

[tresesco]

Hi Searle,

Though this is not omtimised solution, still i think you want something like :

=if(aggr(Count({<Month=,Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)-30))"}>} UniqueID),BP)>3

AND NOT

(

=if(aggr(Count({<Month=,Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)-30))"}>} UniqueID),BP)>3
AND
aggr(sum({<Lock_Valid_To={">=$(=date(today(0)))"}>} Lock_Count),BP)>0,BP)

)

OR
(aggr(sum({<Lock_Valid_To={">=$(=date(today(0)))"}>} Lock_Count),BP)>0

AND NOT

(

=if(aggr(Count({<Month=,Date = {"<=$(=date(max(Date)))>=$(=date(max(Date)-30))"}>} UniqueID),BP)>3
AND
aggr(sum({<Lock_Valid_To={">=$(=date(today(0)))"}>} Lock_Count),BP)>0,BP)

)

)

,BP)

Plz check the parenthesis...

Regards,

tresesco

[agsearle]

I can see what you are suggesting, but this returns no data at all?

[tresesco]

I wanted to mean :

IF(NOT(condition1 AND Condition2),your exsisting expression(with OR)).

i.e you have to put an extra IF at the beginning of your expression (with OR).

Hope this would help you.

Regards,

tresesco

[jonathandienst]

Andrew

If I understand you correctly, you are looking for an "exclusive OR" (ie true if one or other of the values is true, false if both values are true or both values are false).

If that is it, replace the OR with XOR.

Hope that helps.

Jonathan

[agsearle]

Tried both suggestions, and still not working.

Jonathan, this is exactly what I want to do, but XOR doesn't seem to do the trick.

[tresesco]

Hi Andrew,

Though both the solutions were right(i believe), Jonathon's one was surely better. if these don't work, probably its a validation issue or something else. Can you please upload your sample application?

[jonathandienst]

Andrew

Have you tried enclosing the terms in brackets and/or checked that the brackets are correct? This is often not that easy to see, even with the bracket hghlighting. XOR should work in this scenario.

Jonathan