topic Re: Set analysis with 'Or' in QlikView

Set analysis with 'Or'

Anonymous — Fri, 22 May 2015 15:08:39 GMT

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

swuehl — Fri, 22 May 2015 15:10:03 GMT

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

Anonymous — Fri, 22 May 2015 15:10:29 GMT

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

Re: Set analysis with And

Anonymous — Fri, 22 May 2015 15:16:12 GMT

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

Anonymous — Fri, 22 May 2015 15:16:27 GMT

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

swuehl — Fri, 22 May 2015 15:20:20 GMT

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

Fri, 22 May 2015 15:20:46 GMT

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

Anonymous — Fri, 22 May 2015 15:23:20 GMT

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

Fri, 22 May 2015 15:28:40 GMT

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

Re: Set analysis with And

Anonymous — Fri, 22 May 2015 15:32:17 GMT

Edited post for (hopefully) more clarity

Re: Set analysis with And

Anonymous — Fri, 22 May 2015 15:35:12 GMT

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

Re: Set analysis with And

Anonymous — Fri, 22 May 2015 15:36:00 GMT

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

Re: Set analysis with And

Anonymous — Fri, 22 May 2015 15:37:07 GMT

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'

Fri, 22 May 2015 15:46:28 GMT

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'

Anonymous — Fri, 22 May 2015 15:47:37 GMT

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 'Or'

Fri, 22 May 2015 15:52:10 GMT

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'

Anonymous — Fri, 22 May 2015 15:53:32 GMT

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'

Anonymous — Fri, 22 May 2015 15:55:04 GMT

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'

Anonymous — Fri, 22 May 2015 15:56:39 GMT

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 'Or'

Anonymous — Fri, 22 May 2015 15:57:33 GMT

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