Details about this thread
  • State Not Answered
  • Replies 4 replies
  • Subscribers 309 subscribers
  • Views 2100 views
  • Users 0 members are here
Related content from around SugarClub

How to calculate ratio?

MAtthieu LeBoeuf

Hello,

I work on sugar and I would like to calculate ratio(like how many leads became opportunity(conversion ratio) or a win/loss ratio for the opportunities).

Mathematicly speaking, I need to make a division.

I tried on regular reporting but the section where you can do average/count/etc. does not offer "divide".

Can it be done in regular reporting?

Otherwise, can it be done in advanced reporting? Will it be necessarty to have SQL query that will calculate the ratio?

Do you have an example? Can't find anything in documentation.

Thanks!

Parents
  • 0

    Hello Matthieu,

    You could probably use Advanced Reports ( see documentation ) to do this. However, this does require a level of comfort with SQL queries. Another option would be to create a new field and use a formula to calculate the value. Then you can add this field to the report to get the information you want.

    Alternatively, just create a custom logic hook to calculate the value and populate a custom field. This requires comfort with PHP though. The easiest and most straight forward solution would be to use the formula as it doesn't require coding skills but it is limited to the capabilities of Sugar.

    I hope this gets you set in the right direction

    Jason Smith

  • 0 in reply to Jason Smith

    Great,

    I was hoping for a quick fix in the report section.

    I will go and have a look at the advanced report.

    I was also looking into the calculated field section.

    Hope it works!

    Tx for the tip!

    Matthieu

  • 0 in reply to MAtthieu LeBoeuf

    One idea for a quick fix to this - using calculated fields for your example of Opportunity win/loss ratio:

    Create a new Integer field in Opportunities, make it a calculated field based on the status of your Won/Lost status using the ifElse() logic. The goal here is to have this integer field = 1 if Won, and = 0 if Lost (and perhaps leave it blank if the opportunity is still ongoing).

    Then you should be able to run a regular summation (or w/ details) report and use the AVG: of your new calculated field.

    Let's say you had 5 Won and 5 Lost Opportunities, the result of this field in a report would be 1, 1, 1, 1, 1, 0, 0, 0, 0, 0 - which would average out to 50%.

    I haven't tested this out, but it seems like it should work. And then the same logic would apply to your Lead conversion ratio field.

    Let us know if you need help with the ifElse() conditions.

Reply
  • 0 in reply to MAtthieu LeBoeuf

    One idea for a quick fix to this - using calculated fields for your example of Opportunity win/loss ratio:

    Create a new Integer field in Opportunities, make it a calculated field based on the status of your Won/Lost status using the ifElse() logic. The goal here is to have this integer field = 1 if Won, and = 0 if Lost (and perhaps leave it blank if the opportunity is still ongoing).

    Then you should be able to run a regular summation (or w/ details) report and use the AVG: of your new calculated field.

    Let's say you had 5 Won and 5 Lost Opportunities, the result of this field in a report would be 1, 1, 1, 1, 1, 0, 0, 0, 0, 0 - which would average out to 50%.

    I haven't tested this out, but it seems like it should work. And then the same logic would apply to your Lead conversion ratio field.

    Let us know if you need help with the ifElse() conditions.

Children