# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

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

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

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.

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.

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

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}