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.
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:
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.
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:
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.