Forum Discussion

NickGreen's avatar
NickGreen
Qrew Member
2 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
------------------------------

6 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 months ago

    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 (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • NickGreen's avatar
      NickGreen
      Qrew Member
      2 months ago

      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's avatar
        MarkShnier__You
        Qrew Champion
        2 months ago

        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?



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