Set analysis with 'Or'

[Anonymous]

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

[swuehl]

Maybe like this

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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.

[swuehl]

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

[#7]

"[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

[Anonymous]

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

[#9]

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}

Please let me know

[Anonymous]

Edited post for (hopefully) more clarity