Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
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

23 Replies
Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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?

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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!

Anonymous
Not applicable
Author

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