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); $ConversionRateThe 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
------------------------------
Nick Green
------------------------------
