Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

danmercer
Not applicable

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

Tags (3)
1 Solution

Accepted Solutions
mov
Not applicable

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

23 Replies
swuehl
Not applicable

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

mov
Not applicable

Re: Set analysis with And

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

danmercer
Not applicable

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.

danmercer
Not applicable

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.

swuehl
Not applicable

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

Not applicable

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

mov
Not applicable

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

Not applicable

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}

Please let me know

danmercer
Not applicable

Re: Set analysis with And

Edited post for (hopefully) more clarity