Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hmm. The first formula returned all 0's, the second returned null values.
I edited the post. Does that help clear things up?
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])
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?
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
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))
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])
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.
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!
That's expected. When you use calculated dimension - you have to check the "suppress" box.