This is an example of using the Custom Display As element added in 2017.2 to correctly calculate the variance on Custom and Metric accounts.
This sample shows how to add an Account Attribute then reference it in a formula to flip the results of the out-of-the-box variance calculation.
Tips covered in this video:
- Attribute name requirements to be used in formulas
- Using spaces and comments to make your formulas easier to follow
- Using the fast Iff statement and testing for the most common case with exception as else for best performance
- Search function to check for a value in the Account Attribute name, returns 0 if not found
The user interface might not match what you see in this video.
1. Create a new Account Attribute VarCalc, the attribute name cannot contain spaces or special characters.
2. Create new Account Attribute value named Flp.
3. Assign the Account Attribute Flp to the Custom account Headcount.
4. Update report by adding a new Segment.
5. Select Display As and drag Custom to the report in the new segment.
6. Right click on Custom and select Formula Assistant.
7. Clear the existing formula.
8. Enter the following formula:
Note: Alternative syntax such as this.acount.varcalc.name may be used in place of the search function.
9. Review report to confirm the Metric account has been flipped to positive 2 from a negative 2.