One common exception is My SQL’s measures can take any valid SQL expression that results in a number or integer.
For example, the following Look ML creates a field Change In Inventory by subtracting a field called Number Of Items Returned from Number Of Items Sold: measures can take any valid SQL expression that results in a table column, Look ML dimension, or combination of Look ML dimensions.
AVGW, COUNTIF, COUNTIFS, INDEX, JOIN, LOOKUP, MATCH, PRORATE, SUMIF, SUMIFS ANCESTORS, CHILDREN, PARENT Evaluates if a set of logical expressions are true or false.
If any expression is false it will evaluate as false.
FIND, LEFT, LOWER, MID, REPLACE, RIGHT, SUBSTITUTE, UPPER, VALUE Manipulate and display dates and times, calculate working days, and so on.
DATE, DATEONLY, DAY, MONTH, NETDAYS, NETWORKDAY, NETWORKDAYS, TODAY, WEEKDAY, WEEKNUMBER, WORKDAY, YEAR, YEARDAY Count or sum values that meet specific criteria, calculate weighted averages, prorate.
The @cell parameter performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range; this makes your formula more efficient.
For example, using the sample data, if you wanted to sum the cost of all tasks whose due date occurred in 2014, you would create the following formula: =SUMIF([Due Date]1:[Due Date]5, YEAR(@cell) = 2014, Cost1: Cost5) The result of this formula is 5.75 (the sum of the cost for all tasks whose due date occurred in 2014).
Suppose you want to calculate the monthly cumulative sum of the transaction quantities.Hierarchy formulas allow you to roll-up information found on child rows of the referenced parent row.If placed on the parent row in the column with the data to be utilized, the syntax is =COUNT(CHILDREN()) with empty parentheses.AVGW(numeric_range, weight_range) The AVGW function takes the following arguments: Performs a logical evaluation and returns a value if the expression evaluates to true.You can also specify that a different value be returned if the expression evaluates to false.
This article provides information on the functions available to Smartsheet.