topic Re: AND /OR operations in set analysis in Visualization and Usability

AND /OR operations in set analysis

Tue, 14 Mar 2017 17:32:47 GMT

Hi,

I have to do and/or operations in set analysis . Code is following:

Count(

{<CurrYear_flag={'Y'} , Prev1Year_flag={'Y'}, Prev2Year_flag={'Y'}>} +

{<CurrYear_flag={'Y'},Prev1Year_flag={'Y'},Prev2Year_flag -={'Y'}> }+

{<CurrYear_flag={'Y'},Prev1Year_flag -={'Y'},Prev2Year_flag={'Y'}>}

distinct ACCOUNT)

Can you please let me know right syntax to do this. only first set {<CurrYear_flag={'Y'} , Prev1Year_flag={'Y'}, Prev2Year_flag={'Y'}>} is working. Rest 2 are giving 0 in results. Prev2Year_flag -={'Y'} is not working I try individually. It has data Y, - . I am looking for not 'Y' data. It is not working with Prev2Year_flag ={'-'} too

Thanks,

Akanksha

Re: AND /OR operations in set analysis

swuehl — Tue, 14 Mar 2017 17:37:42 GMT

First, I think you should use only a single set expression (single pair of {} )

Count(

{<CurrYear_flag={'Y'} , Prev1Year_flag={'Y'}, Prev2Year_flag={'Y'}> + <CurrYear_flag={'Y'},Prev1Year_flag={'Y'},Prev2Year_flag -={'Y'}> + <CurrYear_flag={'Y'},Prev1Year_flag -={'Y'},Prev2Year_flag={'Y'}>}

distinct ACCOUNT)

Not sure if the parts are ok. Do you maybe need to handle NULL (which may appear as '-' on different places)?

Re: AND /OR operations in set analysis

Tue, 14 Mar 2017 17:59:34 GMT

Ya I made it single pair now.

Can you please let me know in this case how will I check for null

Re: AND /OR operations in set analysis

sunny_talwar — Tue, 14 Mar 2017 18:05:25 GMT

How about this:

Count({<ACCOUNT = {"=

(CurrYear_flag = 'Y' and Prev1Year_flag = 'Y', Prev2Year_flag = 'Y') or

(CurrYear_flag = 'Y' and Prev1Year_flag = 'Y' and Prev2Year_flag <> 'Y') or

(CurrYear_flag = 'Y' and Prev1Year_flag <> 'Y' and Prev2Year_flag <> 'Y')>}

DISTINCT ACCOUNT)

Re: AND /OR operations in set analysis

Tue, 14 Mar 2017 18:22:17 GMT

<> 'and' doesn't work in set analysis

Re: AND /OR operations in set analysis

sunny_talwar — Tue, 14 Mar 2017 18:23:49 GMT

Have you tested this or are you talking from experience? From what I know they both do work as a search string in set analysis

Re: AND /OR operations in set analysis

Tue, 14 Mar 2017 18:27:20 GMT

Yes I tested it. Its showing error in set modifier

Re: AND /OR operations in set analysis

sunny_talwar — Tue, 14 Mar 2017 18:30:11 GMT

Missed a double quote and curly bracket at the end:

Count({<ACCOUNT = {"=

(CurrYear_flag = 'Y' and Prev1Year_flag = 'Y', Prev2Year_flag = 'Y') or

(CurrYear_flag = 'Y' and Prev1Year_flag = 'Y' and Prev2Year_flag <> 'Y') or

(CurrYear_flag = 'Y' and Prev1Year_flag <> 'Y' and Prev2Year_flag <> 'Y')"}>}

DISTINCT ACCOUNT)

Re: AND /OR operations in set analysis

Tue, 14 Mar 2017 18:47:06 GMT

Its giving 0 in result. I have pasted pic below. Seems like something wrong in brackets. last Closing bracket is not matching with red highlighted one's . Can you please check.

Re: AND /OR operations in set analysis

sunny_talwar — Tue, 14 Mar 2017 18:53:40 GMT

My bad... once more change

Count({<ACCOUNT = {"=

(CurrYear_flag = 'Y' and Prev1Year_flag = 'Y' and Prev2Year_flag = 'Y') or

(CurrYear_flag = 'Y' and Prev1Year_flag = 'Y' and Prev2Year_flag <> 'Y') or

(CurrYear_flag = 'Y' and Prev1Year_flag <> 'Y' and Prev2Year_flag <> 'Y')"}>}

DISTINCT ACCOUNT)

Re: AND /OR operations in set analysis

Tue, 14 Mar 2017 19:04:07 GMT

Awesome! its working now.

Can you please explain logic of " being used here

Re: AND /OR operations in set analysis

sunny_talwar — Tue, 14 Mar 2017 19:08:34 GMT

Logic of "?

Re: AND /OR operations in set analysis

Tue, 14 Mar 2017 19:11:58 GMT

double quotes used

Re: AND /OR operations in set analysis

sunny_talwar — Tue, 14 Mar 2017 19:14:08 GMT

Just the way search string works... look here:

A Primer on Set Analysis