Calculated Variables
QWAdmin Help Files
A Calculated variable has its value derived based on a formula that you specify.
Ccalculated variables do not appear on the Add/Edit/Insert/Copy screens as the value is determined when the Save key is pressed.
Calculated variables can be charted, used in other calculations and can have rules and limits assigned to it.
The length of the variable must include an allowance for a decimal point and sign if required.
Click on the Formula button to display a popup of available @functions.
Right mouse click to display a list of all the variables you can use in the formula
QWAdmin will scan the formula, when the Save button is clicked, to ensure the formula has balanced sets of brackets [ ] and parenthesis ( )
If @functions are used in the formula they are checked against a list of valid functions.
If an error is detected in the formula you will not be able to save the variable until the formula is corrected.
Creating a Calculated Formula
A formula can be as simple as a constant such as "CALC: 14", which sets the variable to a value of "14".
A formula can also be a complex combination of constants, mathematical operators (+  / * ^), V##variable reference numbers, QW @ functions and parenthesis ( ).
Calculated variables make use of Input and Output files. In the following case V17, a Calculated variable, is used as a counter.
INPUT FILENAME... SHIFTTOT.V17
OUTPUT FILENAME.. SHIFTTOT.V17
CALC: V17 + V8
At the time the Add screen is selected, QW reads the value stored in SHIFTTOT.V17, example 128, and assigns V17 the value 128. When the Insert key is pressed to save the entered data the value of V8, example 13, is added to the value of V17, 128. The new value of 128 + 13 = 141 is stored in the OUTPUT FILENAME SHIFTTOT.V17. The next time the Add data key is pressed the value of 141 is stored in V17 and the process is repeated.
Note:QW computes calculated variables in a set sequence, from the lowest variable number to the highest, V3, V4,V5..V99. If you are using a Calculated variable in the formula of a second Calculated variable make sure the one used in the formula has a lower V## or the result of the calculation will be incorrect.
The following is a description of the conventions you must follow to create Calculated variables and maximize their use.
Operator 
Description 
Example 
+ 
adds two values together 
V16 + 23 
 
subtracts one value from another 
V16  23 
* 
multiplies one value by another 
V16 * 1.33 
/ 
divides one value by another 
V16 / V12 
^ 
raises a value to the power of another 
V12^2 
V##
Variable reference numbers, are used in QW to give you access to the values of other variables entered or computed in the same record or entry, or previous values in the case of special @ Moving value or Historical Record functions.
You can use variable types Numeric, Calculated, Average, Range and Popup in a formula. A Popup variable used in a formula will return the value of the key selected for that popup.
Parenthesis ( )
Use parenthesis to control the order in which QW calculates the formulae you input. QW follows the standard conventions on order of operations. They are multiplication, division and rising to the power will be computed in the order they occur followed by addition and subtraction in the order they occur. Depending how you use, or not use, parenthesis can render a result you may not expect.
For example:
3+4*6^2 = 379
((3+4)*6)^2 = 1764
3+((4*6)^2) = 579
(3+4)*(6^2) = 252
@ Functions
QW has a number of predefined functions that you can make use of to simplify the formulas you define. These functions have specific formats that you must follow. A QW function is preceded by the @ symbol to indicate it is a function. All functions have a function name followed by an expression, and in the case of Moving Functions, a restriction. The expression and restriction if any, are enclosed in square brackets [ ].
@FUNCTION NAME[expression, restrictions if any]
The expression can be as simple as just one variable
@SQRT[V18]  the square root of the value of V18
@AVG[V3,V4,V5,V6,V7,V8,V12]  the average value
or, it can be a complex formula including other @Functions.
@ABS[V18  @AVG[V3,V4,V5,V6,V7,V8,V12]]
All V##'s in a list must be separated by a comma ",". In the case of the above example you can use the dot dot, "..", feature to simplify the formula.
@AVG[V3..V8, V12]  computes the same result.
The ".." feature can be used on all consecutive variables in a list.
One @ Function can be used within another @ Function eliminating the need for intermediate variables.
V16=@AVG[V4..V8]
V17=@MIN[V16,10]
These two variables can be replaced by V17=@MIN[@AVG[V4..V8],10]
There are two types of @ Functions:
Single Record @Functions
Use the values from variables entered or calculated for a single Date and Time entry in the ADD or Log screen. Examples of these are @AVG, @RANGE and @TARGET. 
Historical Record @Functions (or moving value functions)
Use the values for a particular variable back in time a fixed number of entries (restriction). The number of historical entries to be used is defined in the @ Moving Function after the V##.
@MAVG[V16,5]
In this example QW will compute the average value of V16 using the last 5 records including the current one plus the four previous entries.
The minimum number of historical values in an @ Moving Function is 2.
The maximum number of historical values in an @ Moving Function is 99 or the number of records (y) currently in memory (Record x of y).

QW automatically bypasses null (no value entered) values in a V## list for an @Function.
For example in the formula CALC: @AVG[V16..V18] where V16=12 V17=13 V18=null
QW will add 12 + 13 = 25 and divide by 2 not 3 because V18 was a null value.
Available @Functions
The following is a list of QW Functions with a brief description and example of each.
Note:
Where [expression] is used it means a function allows complex calculations within the brackets, including other @ Functions.
Where [V##] is used a single V## or numeric constant is permissible.
Where [V##..V##] is used a list or range of V##'s and or numeric constants is accepted.
Where [ ] follows a function name no expression is required but the brackets are required.
Trigonometric Conversions:
For @ATAN, @COS, @SIN and @TAN trigonometric functions, converting to degrees or radians can be accomplished as follows:
Degrees to radians ® multiply the angle (degrees) by PI/180.
Radians to degrees ® multiply the angle (radians) by 180/PI.
Function 
Description / Example 

@ABS[expression] 
Returns the absolute value of the expression Ex: @ABS[V5(V14+V18)] 

@ATAN[V##] 
Returns the ARC TANGENT of angle V## given in radians. Ex: @ATAN[V13] 

@AVG[V##..V##] 
Returns the average value of the variables in the list. Ex: @AVG[V4..V6,V9,V10] 

@CASE[statement,min,max,value,...] 
Uses the result of the statement and returns the assigned value that matches the min/max range
Ex: @CASE[@HOUR[ ]+(@MIN[ ] /60),7.50,15.49,10,15.50,23.49,20,23.50,24.00,30,00.01,7.59,30]
would return: a 10 for the 7:30 to 3:30 shift a 20 for the 3:30 to 11:30 shift a 30 for the 11:30 to 7:30 shift 

@CHOOSE[V##,LIST] 
Returns the entry from the LIST depending on the value of V##.
Ex: @CHOOSE[V6,10,20,30]
if V6=0 10 is returned if V6=1 20 is returned if V6=2 30 is returned if V6 > the number of entries in the list then null (no entry) is returned. 

@COMPLYCL%[V##..V##] 
Returns the percentage of variables that meet compliance to Control Limits.
At least one Control Limit must be Fixed.
This function is calculated as: the number of variables that have a value minus the number of variables found to be outside of Control Limits divided by the total number of variables times 100.
Ex: @COMPLYCL%[V12..V18] 

@COMPLYSL%[V##..V##] 
Returns the percentage of variables that meet compliance to Specifications Limits.
At least one Specification Limit must be Fixed.
This function is calculated as: the number of variables that have a value minus the number of variables found to be outside of Specification Limits divided by the total number of variables times 100.
Ex: @COMPLYSL%[V12..V18] 

@COS[V##] 
Returns the COSINE of angle V## given in radians.
Ex: @COS[V13] 

@COUNT[V##..V##] 
Returns the number of nonblank,nonnull variables in the V## list.
Ex: @COUNT[V12..V18] 

Date Functions @YEAR[ ] @MONTH[ ] @DAY[ ] @DOW[ ] 
Returns the year of the week of the current record Returns the month of the week of the current record Returns the day of the week of the current record Returns the day of the week of the current record where: Sun=1 Mon=2 Tue=3 Wed=4 Thu=5 Fri=6 Sat=7
Ex: July 23, 2002 would return YEAR=02 MONTH=07 DAY=23 and DOW=03 

@EXP[expression] 
Returns the natural anti Log value for the expression (e^x).
Ex: @EXP[V12*3] 

@IF[condition,T,F]

Returns either the value for True or False depending on the condition.
Ex: @IF[V5>99,99,V5]
If V5 is greater than 99 then the True value 99 is returned. If V5 is less than or equal 99 then the False value and the value of V5 is returned.
Conditional IF Operators are:
A Practical @IF example to accumulate downtime minutes by shift where:
V3 = Shift code V9 = Minutes Down V11 = Accumulated Minutes Down
The calculation would be @IF [V3 = @MPREV[V3,5], V9 + @MPREV[V11,5],V9]


@INT[expression] 
Returns the integer or whole number
Ex: @INT[(V4*1.23)/4] 

@ISNA[V##] 
Tests a variable for the value null.
If the value is null it returns a value 1.
If the value is not null the value 0 is returned.
Ex: @IF[@ISNA[V5],10,V5] If V5 is equal to null @ISNA returns a value of 1 which to the @IF function is the value for True so 10 is returned. 

@LN[expression] 
Returns the natural log for the expression.
Ex: @LN[V10/2] 

@LCL[V##] 
Returns the Lower Control Limit value for a variable.
Ex: @LCL[V16] 

@LOG[expression] 
Returns the LOG base 10 value for the expression.
Ex: @LOG[V10/2] 

@LSL[V##] 
Returns the Lower Specification Limit value for a variable.
Ex: @LSL[V16] 

@MAVG[V##..V##,nn] 
Returns the Moving Average for variable V## back nn historical points. The minimum value for historical points is 2.
Ex: @MAVG[V9..V11,5] 

@MAX[V##..V##] 
Returns the maximum or highest value from the variables in the list.
Ex: @MAX[V15,V18,V21] 

@MC%OOL[V##..V##,nn] 
Returns the percentage of values calculated to be outside the control limits for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MC%OOL[V9..V11,5] 

@MCOUNT[V##..V##,nn] 
Returns the number of nonnull values for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MCOUNT[V9..V11,5] 

@MCPK[V##..V##,nn] 
Returns the Moving Capability Clearance for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MCPK[V9..V11,5] 

@MCR[V##..V##,nn] 
Returns the Moving Capability Ratio for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MCR[V9..V11,5] 

@MIN[V##..V##] 
Returns the minimum or smallest value from the variables in the list.
Ex: @MIN[V15,V18,V21] 

@MMAX[V##..V##,nn] 
Returns the maximum or highest value from the variables in the list.
The minimum value for historical points is 2.
Ex: @MAX[V9..V11,5] 

@MMIN[V##..V##,nn] 
Returns the minimum or smallest value for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MMIN[V9..V11,5] 

@MO%OOL[V##..V##,nn] 
Returns the percentage of values observed to be out side of the control limits for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MO%OOL[V9..V11,5] 

@MOD[X,Y] 
Returns the integer remainder of the division X divided by Y.
X and Y can be numbers (4) or variables V##.
Ex: @MOD[V5,4]
where V5 has a value of 19 returns a value of 3 (19 divided by 4 remainder 3). 

@MPREV[V##,nn] 
Returns the first nonnull value for variable V## searching back nn historical points. The search starts with the last or previous record and then continues searching back ##  1 historical points.
The minimum value for historical points is 2.
Ex: MPREV[V16,5] 

@MRANGE[V##..V##,nn] 
Returns the Moving Range for variable V## back nn historical points (Maximum  Minimum value).
The minimum value for historical points is 2.
Ex: MRANGE[V9..V11,5] 

@MSD[V##..V##,nn] 
Returns the Moving Standard Deviation for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MSD[V9..V11,5] 

@MSUM[V##..V##,nn] 
Returns the total of all values for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MSUM[V9..V11,5] 

@MTZ[V##..V##,nn] 
Returns the Moving Target Z values for variable V## back nn historical points.
The minimum value for historical points is 2.
Ex: @MTZ[V9..V11,5] 

@NA[ ] 
Returns a value of null.
Ex: @IF[V5>99,@NA[ ],V5] if V5 is greater than 99 then the value null, @NA[ ], is assigned. 

@OCTRL[V##..V##] 
Returns the count of variables in the list whose values are found to exceed the control limits set for each variable in the list.
Ex: @OCTRL[V5,V7..V14] 

@OCTRL%[V##..V##] 
Returns the percentage of variables in the list whose values are found to exceed the control limits set for each variable in the list.
Ex: @OCTRL%[V5,V7..V14] 

@OSPEC[V##..V##] 
Returns the count of variables in the list whose values are found to exceed the specification limits set for each variable in the list.
Ex: @OSPEC[V5..V9,V14] 

@OSPEC%[V##..V##] 
Returns the percentage of variables in the list whose values are found to exceed the specification limits set for each variable in the list.
Ex: @OSPEC%[V5..V9,V14] 

@PI[ ] 
Returns the constant 3.1415926.
Ex: V18 * @PI[ ] 

@POP[V##..V##,nn] 
Returns statistics for the population defined in the range of variable back nn records.
The use of the @POP function in a Calculated variable will trigger QW to display data on the Control Chart differently than other Calculated, Numeric Average or Range type variable types.
Charting an @POP variable will show a vertical bar with the maximum value of the sample size at the top, the minimum value of the sample size at the bottom and the average as a blue dot.
Ex: @POP[V9..V11,5] 

@RANGE[V##..V##] 
Returns the range (Maximum value  Minimum value) of the variables in the list.
Ex: @RANGE[V4..V6,V9] 

@RULE[V##..V##] 
Returns the count of rule violations for the variables in the list.
Ex: @RULE[V14..V18] 

@RULE%[V##..V##] 
Returns the percentage of variables in the list that had rule violations.
Ex: @RULE%[V14..V18] 

@SD[V##..V##] 
Returns the Standard Deviation for the range of variables in the list
Ex: @SD[V12,V17..V22] 

@SGN[expression] 
Determines the sign of the expression. (1 for a negative, 0 for zero, 1 for a positive value)
Ex: @SGN[V512] 

@SIN[V##] 
Returns the SINE of angle V## given in radians.
Ex: @SIN[V13] 

@SQRT[V##] 
Returns the Square Root value.
Ex: @SQRT[V16] (using V## * .5 returns the same value) 

@SUM[V##..V##] 
Returns the sum or total of adding together the variables in the list.
Ex: @SUM[V15..V23] 

@TAN[V##] 
Returns the TANGENT of angle V## given in radians.
Ex: @TAN[V13] 

@TARGET[V##] 
Returns the Target value for a variable.
Ex: @TARGET[V16] 

Time Functions @HOUR[ ] @MINUTE[ ] @SECOND[ ] 
Returns the current record hour in 24 hour clock format. Returns the current record minute. Returns the current record second.
Ex: If the time on the current record is 2:10:03 PM
@HOUR[ ] would return 14 @MINUTE[ ] would return 10 @SECOND[ ] would return 3 

@TIMEDIFF[ ] 
Returns the number of seconds since the previous record. Ex: @TIMEDIFF[ ] Use @TIMEDIFF[]/60 to derive minutes Use @TIMEDIFF[]/3600 to derive hours. 

@UCL[V##] 
Returns the Upper Control Limit value for a variable.
Ex: @UCL[V16] 

@USL[V##] 
Returns the Upper Specification Limit value for a variable.
Ex: @USL[V16] 

@XBAR[V##..V##] 
Returns statistics for the population defined in the range of variables. The use of the @XBAR function in a Calculated variable will trigger QW to display data on the Control Chart differently than other Calculated, Numeric Average or Range type variable types.
Charting an @XBAR variable will show the average and the Range chart will be displayed below the Control Chart.
The @XBAR function differs from the @AVG function by taking the sample size into consideration.
Ex: @POP[V9..V11,5] 