Forum Discussion

Gelo's avatar
Gelo
Qrew Trainee
4 years ago

Application Status Funnel

Hello everyone - I posted a question yesterday and you guys were extremely helpful so I figure I'll present another issue we've been struggling with. 

We are a mortgage lender. We want to create an "Application Status Funnel" using the "Funnel" graph. As a customer application advances through the application process, it will move further down the funnel until eventually falling out of it once it is complete. From the top of the funnel to the bottom the categories would be: Application Received -> Application Approved -> Awaiting Stips -> Cleared to Close -> Loan Closed. Each one of these categories are also their own fields. They are "Date" fields. 

Therefore, from the master data list if there is a date for Application Received and then after the next data refresh, there is a date listed under Application Approved - we would want it to move to that portion of the funnel - etc, etc. It would be like that for all records. 

It's just tricky looking at the chart creation page because it doesn't seem like what we are trying to do can happen. We are struggling with what we would select for Funnel Grouping too. The data values would be # of fields I'm assuming. 

Is what we are trying to accomplish even possible?

Thank you in advance! ​

------------------------------
Gelo
------------------------------
Formulas and functions
Tables and fields
  • Gelo's avatar
    Gelo
    Qrew Trainee
    4 years ago
    You can disregard this ---- our third-party devs were able to get something close that we would need. I think we are going to be fine. 


    ------------------------------
    Gelo
    ------------------------------
  • ChristopherWhea's avatar
    ChristopherWhea
    Qrew Cadet
    4 years ago
    Hi Gelo,

    Glad you got it sorted.  FWIW, this should be pretty straightforward to accomplish.  You'd just need to create your funnel grouping field, something like Application Status and make it a Formula - Text.  Then create the logic based on your 5 date fields that indicate your Application Status.  Lots of ways to do this...nested If statement, a Max formula, etc.  Others may have a more elegant solution but I'd start with something like:

    var number Received = If(isNull([Application Received]),0,1);
    var number Approved = If(isNull([Application Approved]),0,2);
    var number Stips = If(isNull([Awaiting Stips]),0,3);
    var number Cleared = If(isNull([Cleared to Close]),0,4);
    var number Closed = If(isNull([Loan Closed]),0,5);
    var number Status = Max($Recieved, $Approved, $Stips, $Cleared,$Closed);

    Case($Status,
         1, "Application Received",
         2, "Application Approved",
         3, "Awaiting Stips",
         4, "Cleared to Close",
         5, "Loan Closed"
    )

    You may want different Status cases, e.g. If Received = 1, "Awaiting Approval" or something like that.

    ------------------------------
    Chris Wheatley
    ------------------------------