NickGreen
Qrew Member
7 months ago

# Conversion rate within a Guage, possible?

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]) >= Today() - Days(30) and [Sales Agent Name] = "Agent Name", 1, 0);`
`                   `
`var number NoSale = If([Sales Agent Name] = "Agent Name" and IsNull([Agreement Signed]), 1, 0);`
` `
`var number ConversionRate = If((\$Sale + \$NoSale) <> 0, (\$Sale / (\$Sale + \$NoSale)) * 100, 0);`
` `
`\$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
------------------------------

• 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]) >= Today() - Days(30) and [Sales Agent Name] = "Agent Name", 1, 0);

\$Sale

------------------------------
mark.shnier@gmail.com
------------------------------
• 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
------------------------------

• 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

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?

------------------------------