1

Formula Help

Hello,

I am trying to build a modeled data set so that I can add a formula "field" as a column to the data. I built the Salesforce version of this formula as a report formula in Salesforce, and it works perfectly. However, when I create the data source in Klipfolio, it pulls every Salesforce report column from the report except the report formula column. So, I decided to go with the modeled data route instead.

I re-wrote the formula from scratch, then went back and compared it to the Salesforce version and made sure it matched exactly (with the exception of the variable names being different, obviously). However, when Klipfolio isn't processing the formula quite right. It's skipping two of my conditions and I can't figure out why.

For context, my data is showing what prediction my predictive tool made and what the actual outcome ended up being. This new formula column is designed to categorize the prediction into one of a few categories, listed below (along with criteria):

  • Category 1: "Accurate - Authorization." The prediction and outcome are the same (thus, accurate) and the prediction was anything other than "None - Does Not Meet MNC."
  • Category 2: "Accurate - Denial." The prediction and outcome are the same (accurate) and the prediction was "None - Does Not Meet MNC."
  • Category 3: "Pending." If the prediction and outcome aren't the same (and therefore aren't already captured in Categories 1-2) and the prediction wasn't blank/null and the outcome is blank/null.
  • Category 4: "Inactive." If not categories 1-3, and the Stage is either (Lost, Unqualified, or Needs Referral) and the prediction is not blank/null and the outcome is blank/null.
  • Category 5: "Still Active." If not categories 1-4, and the Stage is not (Lost, Unqualified, or Needs Referral) and the prediction is not blank/null and the outcome is blank/null.
  • Category 6: "Inaccurate - Authorization." If none of the above applies, and the prediction is not "None - Does Not Meet MNC."
  • Category 7: "Inaccurate - Denial." If none of the above applies and the prediction is "None - Does Not Meet MNC."

The only categories that work as expected are Categories 1, 2, 6, and 7. So, I suspect there's something about the way Klip reads NOT or OR functions (which are included in the erroneous Category formulas), but I can't figure it out. Here's the code I'm using:

IF(
AND(
@E:E=@F:F,
NOT(@E:E="None - Does Not Meet MNC")),
"Accurate - Authorization",
IF(
AND(
@E:E=@F:F,
@E:E="None - Does Not Meet MNC"),
"Accurate - Denial",
IF(
AND(
@D:D="Admitted",
NOT(@E:E=""),
@F:F=""),
"Pending",
IF(
AND(
OR(
@D:D="Lost",
@D:D="Unqualified",
@D:D="Needs Referral"),
NOT(@E:E=""),
@F:F=""),
"Inactive",
IF(
AND(
NOT(
OR(
@D:D="Lost",
@D:D="Unqualified")),
NOT(@E:E=""),
@F:F=""),
"Still Active",
IF(
NOT(@E:E="None - Does Not Meet MNC"),
"Inaccurate - Authorization",
IF(
@E:E="None - Does Not Meet MNC",
"Inaccurate - Denial",
"FORMULA ERROR")))))))

Finally, here's a screenshot to help you see the errors I'm talking about. You can see the Stages, the prediction (the column that starts with "IQ Authorizatio...") and the outcome (the column that starts with "Acutal Authoriz..."). The blank outcome, along with a stage that isn't Admitted, Lost, Unqualified, or Needs Referral, should result in a Prediction Type of "Still Active." Yet, it appears to only be evaluating if the prediction matches the outcome, and providing an Inaccurate prediction type:

Any help is greatly appreciated, thank you!

1 comment

  • Avatar
    Kalyani Khandelwal Official comment

    Hi Blake, 

    Wow! great effort on the formula! 

    The only thing that needs change here is the use of BLANK() instead of the empty string "". 

    The empty string is different than the blank values.

    Hope that helps. 

Please sign in to leave a comment.