Home : Year over Year Variance Utility
Q14391 - INFO: Year over Year Variance Utility

Year over Year Variance Util displays values for a location and date and the previous years value.  It then calculates a relative percent difference (variance) between the values.  It allows for Result Comments to be entered to explain the variances. 

In order to display the menu option you must insert the following record into the OPSROOT.GENERALSETTINGS table.



Variance Calculation:

abs(Current Value - Previous Value)/Previous Value * 100

Comment Required:

If Show is set to Variables with % variance >= x the utility will display variables based on the following rules (i.e. is a Comment Required):

  • If a variable is calculated it is NOT SHOWN.
  • If a variable additional info field NEWVAR (VARDESC_I.NEWVAR) is set to any value the variable is NOT SHOWN
  • If a variables variance calculation is greater than entered threshold (default of 10) the variable is shown.
  • If Previous Value is No value (blank) and the Current Value has a value it is considered to have a variance > entered threshold and the variable is shown.
  • If Current Value is No value (blank) and the Previous Value has a value it is considered to have a variance > entered threshold and the variable is shown.


The YOYNULLEQZERO OPSROOT General Setting allows you to specify that No values (blanks) are treated as zeros.  This is on by default.  If this setting is set to '0', a Null will be considered > entered threshold if the other value is zero.  If set to '1' or record is removed from GeneralSettings table, 0 and Null (blanks) will be considered equal. 




Status is RED when any variables with a variance greater than threshold do not have a result comment entered.  Status turns Green when result comments are entered for all variables that have a variance greater than threshold.

Tech Notes:

The Utility will also display in the status bar the variable additional info fields FormOrder and FormForeColor (VarDesc_I.FormOrder, VarDesc_I.FormForeColor)

When Result Comments are saved, if the FACILITY_I.LocName_VRS_ATS field exists it will check that all required comments have been entered and will update the FACILITY_I.LocName_VRS_ATS with the current time and the  FACILITY_I.LocName_VRS_AU with the currently logged in username. 

LocName is replaced with Location Name selected.  Example, if location is TEMPLATEWW then the fields would be:

  • TEMPLATEWW_VRS_ATS type DateTime
  • TEMPLATEWW_VRS_AU type NVARCHAR(50) or NVARCHAR2(50) in Oracle.





Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?


Tell us why you rated the content this way. (optional)
Approved Comments...
No user comments available for this article.
Created on 9/28/2016 11:35 AM.
Last Modified on 4/18/2017 3:57 PM.
Last Modified by Scott Dorner.
Article has been viewed 3288 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article