The IF function is one of the most popular functions in Excel. With Malartu, it's now combined with the power of BI.
With our IFS function, you can set a nearly infinite number of "tests" against any metric (including those you have custom calculated) and return any value.
Just like in Excel, Malartu will return the value for the first test that "wins." For example, you can set an IFS test to say: "If Current Ratio is less than 1, return 10" - giving this current ratio a score of 10. You can multiple tests to allow for multiple scores.
How to setup your first IFS tests
Navigate to your custom metric builder, then select: Function (under the formula bar) > IFS
You should now see a screen that looks like this:
To build your first test, first select a metric you would like to test against by using the typical metric navigation for custom metrics.
In this example we will use current ratio.
With your metric selected, design your first test by:
- Selecting a qualifier (<, >, =, etc.)
- Selecting either a number or another metric to compare agains
- For example, selecting the # sign you can run a test of IF Current Ratio is Greater Than 1. By selecting the Malartu icon, you can run a test for IF Current Ratio is Greater Than "Customer Ratio Z," another metric you created in Malartu.
- Setting a return value
In this example our first test will be: If "Current Ratio" is "Less Than" 1 return "0"
This means that any value for any time period less than 1 will return a 0, but anything greater than 0 will error since there is no test. Therefore, we need to setup another IFS test to complete this logic.
Setting up multiple IFS tests to complete logic
To setup another test, select "+Add another test" and go through the same process as above, this time making sure that all possible scenarios are accounted for.
REMEMBER: The first of your IFS tests that "wins" will be returned as the final result.
In this example, I am going to setup 3 tests for Current Ratio:
- If "Current Ratio" is "Less Than" 1 return "0"
- If "Current Ratio" is "Less than" 2 return "10"
- If "Current Ratio" is "Greater than or equal to" 2 return "20"
With these tests, values below 1 will not have a score, values between 1 and 2 will get a 10, and anything above 2 will get a 20.
Pro Tip: It's helpful to think of your IFS tests as gates that values can either pass by or stand in queue. For example, a Current Ratio of 1.25 would pass by the first test/gate, because it is not less than 1, but would stop at the second gate, because it is less than 2.
It's easy to accidentally write IFS tests that catch all values after the first test, so make sure your test flow from top to bottom makes sense before diving further into a troubleshooting process.