Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
craigstegercraigsteger 

Merging rows in a report using custom summary formulas?

I've created a report that shows my opportunity pipeline by probability but I'm trying to merge a couple of the rows together and am looking for some help on how to do it.  I'd like to combine the 10% and 20% opportunities into a single row of data so the total monthly and grand totals are combined. 

 

 

Probability Chart

 

Somehow the last row of date would show the following:

10-20%    $3,000      $468,686.35     $602,427.38        $1,074,113.73

 

 

Any help that can be provided would be great.  Thanks!

JakesterJakester
You could create a custom formula field that basically says "combine 10-20%" and show the rest of the percentages normally, and then you could group on that. If you need to continue to show 10 and 20% separately as well as group them together, then you could group by Probability the way you are now and then group by the custom formula field, too. It would repeat a little, but it shouldn't be too bad.
craigstegercraigsteger
Thanks for the quick reply Jakester.  What I'm struggling with is how to write the formula to combine the 10% and 20%.  Any help you could provide there?
JakesterJakester

Untested, I think it would be something like this:

 

 

case( Probability,
           <.21,"10-20%"
           <.31,"30%"
           <.41,"40%","50%"
)
 
 
 
 
 


Craig StegerCraig Steger
I've tried this formula a couple of times but I keep getting a syntax error.  Do you have any ideas on what might be causing that?  Really appreciate the help in troubleshooting this issue I have.


JakesterJakester
What's the syntax error say?


craigstegercraigsteger
The error I receive is:
 
Error: Syntax error. Found '<'


JakesterJakester
I think you can't actually use "<" with a case function, so maybe something like
 
 
if(Probability <.21,"10-20%",
if(Probability <.31,"30%",
if(Probability <.41,"40%",50%
)
 
 You'd obviously repeat this pattern all the way to 100%, instead of just to 50%
 
 
 


craigstegercraigsteger
This is where I am now and am getting a different error.
 
if(PROBABILITY <0.21,"10-20%",
if(PROBABILITY <0.31,"25-30%", 
if(PROBABILITY <0.51,"40-50%",
if(PROBABILITY <0.81,"75%",
if(PROBABILITY <0.91,"90%",
if(PROBABILITY <1.00,"100%"))))))
 
Error: Incorrect parameter for function <(). Expected Object, received Number
 
 


JakesterJakester
I can't read it because you didn't use the Code button on the toolbar. Can you repost using that?


craigstegercraigsteger
 
if(PROBABILITY <0.21,"10-20%",
if(PROBABILITY <0.31,"25-30%", 
if(PROBABILITY <0.51,"40-50%",
if(PROBABILITY <0.81,"75%",
if(PROBABILITY <0.91,"90%",
if(PROBABILITY <1.00,"100%"))))))
Sorry about that.  Here's the code.


JakesterJakester
Try
 
 
if(PROBABILITY <0.21,"10-20%",
if(PROBABILITY <0.31,"25-30%", 
if(PROBABILITY <0.51,"40-50%",
if(PROBABILITY <0.81,"75%",
if(PROBABILITY <0.91,"90%",)))),100%)
 
 
 
 


craigstegercraigsteger
I just tried that and now I'm getting this error.  Also, should the 100% be in quotes as well?
 
Error: Syntax error. Found ')'


JakesterJakester
Yes, it should be in quotes - I'm sorry, but I don't think I'm going to be able to give you the perfect formula. Now that you have the idea, start simple. Try just doing one If() function, and build on that, testing along the way.