Set Analysis Complex And/Or

[#1]

I have the below set analysis.  my basic question is, how do you specify the groups of a complex and/or clause?

sum({<TRD_DATE={"$(='>=' & Start_Date & '<=' & End_Date)"}>

  *<CANCEL_EFF_DATE={"$(='>' & End_Date)"}>

  +<BASE_BOOK_VALUE={"=Len(Trim(CANCEL_EFF_DATE))=0"}>}

  BASE_BOOK_VALUE)

For example:

A and B or C

(A and B) or C

A and (B or C)

How do you apply the parentheses?  In my sample above, I assume this is running (A and B) or C because of order of operations, just going left to right.  I need to ensure that it does A and (B or C).

[#2]

I assume a parenthesis around an and function would be the same as:

<A,B>+<C>

But i'm not sure how to do an or inside parenthesis.

[Nicole-Smith]

Try:

sum({<TRD_DATE={"$(='>=' & Start_Date & '<=' & End_Date)"}, CANCEL_EFF_DATE={"$(='>' & End_Date)"}>

  +<BASE_BOOK_VALUE={"=Len(Trim(CANCEL_EFF_DATE))=0"}>}

  BASE_BOOK_VALUE)

[#4]

That puts the parenthesis around the and function, not the or, right? or does it end up being (A or C) and (B or C)?

[Nicole-Smith]

Correct

[#6]

i need the parenthesis around the or function though, that's my problem.

[#7]

To step out of academia, I have a very complicated SQL where clause that I need to replicate in set analysis, which melds and/or and lots of parenthesis.  there might be some additional parenthesis at the end, please ignore:

( ( ( T.TRD_DATE between $(vBegin) and $(vEnd) )

          and ( t.effective_date <= $(vEnd) )

          and ( t.cancel_eff_date is null

              or t.cancel_eff_date > $(vEnd) ) )

        or ( ( T.TRD_DATE <= $(vBegin) )

            and ( ( t.effective_date between $(vBegin) and $(vEnd)

                   and ( t.cancel_eff_date is null

                        or t.cancel_eff_date > $(vEnd) ) )

             or ( ( t.effective_date < $(vBegin) )

                     and ( t.cancel_eff_date between $(vBegin) and $(vEnd) ) ) ) ) );

[Nicole-Smith]

I think if you reorder it to this, it might give you what you want (but I can't say for sure without having your .qvw and data):

sum({<BASE_BOOK_VALUE={"=Len(Trim(CANCEL_EFF_DATE))=0"}>+

  <CANCEL_EFF_DATE={"$(='>' & End_Date)"}>*

  <TRD_DATE={"$(='>=' & Start_Date & '<=' & End_Date)"}>}

  BASE_BOOK_VALUE)