Conversion rate within a Guage, possible?

Question

Hi y'all,
I am attempting to work out the conversion rate for our Sales agents to be displayed for management in a nice gauge.
I am partially there; however, the result being returned is not the expected result.
What I am trying to do is get all the records where the Agent is assigned and where the Agreement Signed date/time field is null.
I return the records for the last 30 days where the Agent is assigned and the Agreement Signed is not null, and it excludes any records older than that where the Agreement Signed is not null.
I have this formula:

var number Sale = If(not IsNull([Agreement Signed]) and ToDate([Agreement Signed]) &gt;= Today() - Days(30) and [Sales Agent Name] = "Agent Name", 1, 0);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
var number NoSale = If([Sales Agent Name] = "Agent Name" and IsNull([Agreement Signed]), 1, 0);
&nbsp;
var number ConversionRate = If(($Sale + $NoSale) &lt;&gt; 0, ($Sale / ($Sale + $NoSale)) * 100, 0);
&nbsp;
$ConversionRate

The formula is set to Numeric - Percent with two decimal points, and the gauge measures Formula 1 - averaged.

For the current user, the Sale = 124 and NoSale = 299.

The formula effectively should return: 124 over 423 × 100 = 29.29% where 29.29% is the expected value. However, QB is returning a value of 15.75%.

Could anyone perhaps advise on where the issue could be?

Thank you,Nick

------------------------------Nick Green------------------------------

markshnier__you · Answer

Can you tell us what the result is when you simply average the value of the $Sale value?

var number Sale = If(not IsNull([Agreement Signed]) and ToDate([Agreement Signed]) &gt;= Today() - Days(30) and [Sales Agent Name] = "Agent Name", 1, 0);

$Sale------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

nickgreen · Answer

Hiya Mark,
The average value for just $Sale comes to 0.16%
The amount of Sales has increased since the initial calculation.
Sale = 126 and NoSale = 302
------------------------------Nick Green------------------------------

markshnier__you · Answer

You say that the report filter is only where the Agreement Signed is not null.
I return the records for the last 30 days where the Agent is assigned and the Agreement Signed is not null,

But the formula for NoSales is&nbsp;
var number NoSale = If([Sales Agent Name] = "Agent Name" and IsNull([Agreement Signed]), 1, 0);

Doesn't that mean that NoSale will always be zero?------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

nickgreen · Answer

For the NoSale I return all the records where the agent is assigned and the but the Agreement Signed is null.
For the Sale I return all records for the last 30 Days where the agent is assigned and the Agreement signed is not null, so effectively it excludes all records where the agent is assigned and the Agreement Signed is not null, to get an accurate value of their current conversion of those records that still need to get the customer on boarded.
I've not set an actual filter on the report, I am letting the formula do the lifting in this case.
So at the moment it is returning actual values, they just appear to be skewed.
------------------------------Nick Green------------------------------

markshnier__you · Answer

I suggest that you either use three report formulas on a table report to calculate Sale, No Sale and conversion rate, or else make three real fields and expose them on a tab;e rpeort to see whats going on.

But you say that you have no filter on the report.&nbsp; Do you have records on the report where the Agent name is not "Agent Name".&nbsp; It seems to me that you need to add that Filter.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Conversion rate within a Guage, possible?