23 Replies Latest reply: May 22, 2015 12:12 PM by Michael Solomovich

# Set analysis with 'Or'

Hi all,

I've having trouble getting a count or sum with set analysis where there is multiple criteria. Here's what I have so far that isn't working.

=Count({\$< [Next Qtr Flag] = {'1'} + [Next2 Qtr Flag]= {'1'} + [Next3 Qtr Flag] = {'1'},[ Probability ]={"*"}-{0}>}[Opp Unique ID])

I need to count or sum the [Opp Unique ID] for when the [Probability] is NOT equal to 0 for each 'QTR' flag.  A very unsophisticated script would be as follows:

=Count({\$< [Current Qtr Flag] = {'1'}, Probability ={"*"}-{0}>}[Opp Unique ID])+

Count({\$< [Next Qtr Flag] = {'1'}, Probability ={"*"}-{0}>}[Opp Unique ID]) +

Count({\$< [Next2 Qtr Flag] = {'1'}, Probability ={"*"}-{0}>}[Opp Unique ID]) +

Count({\$< [Next3 Qtr Flag] = {'1'}, Probability ={"*"}-{0}>}[Opp Unique ID])

I appreciate any help.

Thanks!

All - I absolutely butchered this post in several ways. I had a very inaccurate title and possibly worse text, and you still walked me through it in spite of myself. I can't begin to thank you for not throwing me in front of a bus too badly for my own stupidity.

Message was edited by: Dan Mercer

• ###### Re: Set analysis with And

Maybe like this

=Count({\$< [Next Qtr Flag] = {'1'}, [Next2 Qtr Flag]= {'1'}, [Next3 Qtr Flag] = {'1'}, [ Probability ] -= {0} >}[Opp Unique ID])

• ###### Re: Set analysis with And

If I'm not mistaken, that is trying to count for when all 3 'Qtr" flags are met, which would never happen in this case, as the Qtr flags represent and end date.

• ###### Re: Set analysis with And

I may have misunderstood your requirement:

"I need to count the [Opp Unique ID] when the [Probability] is NOT equal to 0 and the [Next Qtr Flag], [Next2 Qtr Flag], & [Next3 Qtr Flag] are all equal to 1."

and title of post "...with And".

If you are trying to ask for OR logic, maybe (just guessing):

=Count({\$< [Next Qtr Flag] = {'1'}, [ Probability ] -= {0} > + < [Next2 Qtr Flag]= {'1'}, [ Probability ] -= {0} >+ <[Next3 Qtr Flag] = {'1'}, [ Probability ] -= {0} >} [Opp Unique ID])

• ###### Re: Set analysis with And

"[Next Qtr Flag],[Next2 Qtr Flag], & [Next3 Qtr Flag] are all equal to 1."

That is what you have put as your requirement? If that's not the case you might want to reword this line

Joe

• ###### Re: Set analysis with And

Edited post for (hopefully) more clarity

• ###### Re: Set analysis with And

Adding [Current Qtr Flag] = {'1'}:

Count({\$(<[Current Qtr Flag] = {'1'}> + < [Next Qtr Flag] = {'1'}> + < [Next2 Qtr Flag]= {'1'}> + < [Next3 Qtr Flag] = {'1'}>) * < [ Probability ]-={0}>} [Opp Unique ID])

• ###### Re: Set analysis with 'Or'

This is still returning all zeros.  I don’t know if this matters or not, but the “Qtr” flags are grey like a variable.

Dan Mercer

• ###### Re: Set analysis with And

=Count({\$< [Next Qtr Flag] = {'1'}, [Next2 Qtr Flag]= {'1'}, [Next3 Qtr Flag] = {'1'},[ Probability ]-={0}>}[Opp Unique ID])

• ###### Re: Set analysis with And

If I'm not mistaken, that is trying to count for when all 3 'Qtr" flags are met, which would never happen in this case, as the Qtr flags represent and end date.

• ###### Re: Set analysis with And

That means you nee OR not AND.  Try this:

Count({\$(< [Next Qtr Flag] = {'1'}> + < [Next2 Qtr Flag]= {'1'}> + < [Next3 Qtr Flag] = {'1'}>) * < [ Probability ]-={0}>}[Opp Unique ID])

If it doesn't work, then:

Count({\$(< [Next Qtr Flag] = {'1'},[ Probability ]-={0}> + < [Next2 Qtr Flag]= {'1'},[ Probability ]-={0}> + < [Next3 Qtr Flag] = {'1'},[ Probability ]-={0}>) }[Opp Unique ID])

• ###### Re: Set analysis with And

hmm. The first formula returned all 0's, the second returned null values.

• ###### Re: Set analysis with 'Or'

Fixing (forgot to remove parenthesis):

Count({<[Current Qtr Flag] = {'1'}, Probability-={0}> + < [Next Qtr Flag] = {'1'}, Probability-={0}> + < [Next2 Qtr Flag]= {'1'}, Probability-={0}> + < [Next3 Qtr Flag] = {'1'}, Probability-={0}> } [Opp Unique ID])

• ###### Re: Set analysis with 'Or'

That works.  This and the SumIF by Imad both work.  I'm going to flag this is as correct as it uses set analysis.

Thanks!

• ###### Re: Set analysis with And

I am not sure what you are after:

Am I correct when I say you want:

Count([Opp Unique ID])

Where ([Next Qtr Flag] = {'1'} OR  [Next2 Qtr Flag]= {'1'} OR  [Next3 Qtr Flag] = {'1'})  AND [ Probability ]<>{0}

• ###### Re: Set analysis with And

I edited the post.  Does that help clear things up?

• ###### Re: Set analysis with 'Or'

Why not do it like this:

sum(if(([Current Qtr Flag]=1 OR [Next Qtr Flag]=1 OR [Next2 Qtr Flag]=1 OR [Next3 Qtr Flag]=1) AND [Probability]<>0,1,0))

What do u think?

• ###### Re: Set analysis with 'Or'

This seems to work, however it also created a null value in the dimension.  That's a simple fix simply by checking the suppress when value is null box.

• ###### Re: Set analysis with 'Or'

That's expected.  When you use calculated dimension - you have to check the "suppress" box.

• ###### Re: Set analysis with 'Or'

I guess there are records that outside your count criteria .Hence the null values. and yes you can suppress those values by checking the Suppress When Value is null option in the Dimension tab.

Also check and test output so to be sure.

• ###### Re: Set analysis with 'Or'

will it be possible to attach an excel so to use. its difficult when you don't have the data.

I am sure its something simple we are missing. if you are using them in an expression they should be red.

make sure you add the = sign before the sum.

=sum(if(([Current Qtr Flag]=1 OR [Next Qtr Flag]=1 OR [Next2 Qtr Flag]=1 OR [Next3 Qtr Flag]=1) AND [Probability]<>0,1,0))

• ###### Re: Set analysis with 'Or'

Your sum if works beautifully. Thanks so much!  I only flagged Michael's as correct because his used set analysis, which was what I was originally trying to do.

• ###### Re: Set analysis with 'Or'

no worries mate. Glad you found you solution.

• ###### Re: Set analysis with 'Or'

Dan, feel free to change the "correct/helpful" marks, I'm not going to loose sleep over that