Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Complex And/Or

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).

7 Replies
Not applicable
Author

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)

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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)