Type of Calculation & Function

Example

Achievement Percent

Has a built-in divide by zero check to avoid divide by zero errors.

#AchievementPercent([Measures].[Data1 (Actual Sales Sales Amount Q1 2014 to Q3 2014 )], [Measures].[Data2 (Budget Budget Amount Frozen Q1 2014 to Q3 2014)])

• Returns the achievement percentage between two measure items -- in this case, the percent of sales achieved in comparison to the budgeted sales.

• The expression for this function is Measure Item 1 / Measure Item 2 with a divide by zero check. The divide by zero check will return null if Measure item 2, the divisor, is zero or null.

• The expression syntax includes the names (Data1 and Data2) and captions of the specified measure items.

• Recommendation: Set the Format property to a percent and Total property to None.

Divide with Zero Check

#DivideWithZeroCheck([Measures].[Data1 (Daily Sales Amount Wk 38 2014 to Wk 38 2014)], [Measures].[Data2 (Daily Sales Units Wk 38 2014 to Wk 38 2014)])

• Divides two numbers with a divide by zero check.
• The expression for this function is Measure Item 1 / Measure Item 2 with a divide by zero check. The divide by zero check will return null if Numeric Expression 2, the divisor, is zero or null.
• The expression syntax includes the names (Data1 and Data2) and captions of the specified measure items.

Percent of Change

Has a built-in divide by zero check to avoid divide by zero errors.

#PercentOfChange([Measures].[Data1 (Actual Sales Amount Q1 2013 to Q3 2013)], [Measures].[Data2 (Actual Sales Sales Amount Q1 2012 to Q3 2012)])

• Returns the percent of change, also known as the variance percentage, between two measure items or expressions -- in this case, the change between YTD sales for two different years.

• The expression for this function is (Measure Item 1 - Measure Item 2) / Measure Item 2 with a divide by zero check. The divide by zero check will return null if Measure item 2, the divisor, is zero or null.

• The expression syntax includes the names (Data1 and Data2) and captions of the specified measure items.

• Recommendation: Set the Format property to a percent and Total property to None.

Percent Of Total

#PercentOfTotal([Measures].[Data1 (Actual Sales Amount Wk 1 2017 to Wk 37 2017)])

• Returns percent of total for the designated measure item, in this case Actual Sales Amount Wk 1 2017 to Wk 37 2017 (this caption and the measure item name of Data1 are part of the MDX syntax in the expression).

• Recommendation: Set the Format property to a percent.

Percent Of Subtotal

#PercentOfSubtotal([Measures].[Data1 (Actual Sales Amount Wk 1 2017 to Wk 37 2017)])

• Calculates how much a measure item value contributes to the subtotal. This example returns percent of subtotal for the Actual Sales Amount Wk 1 2017 to Wk 37 2017 measure item (the measure item’s caption and its name of Data1 are part of the MDX syntax in the expression).

• Recommendation: Set the Format property to a percent.

Cumulative Percent Of Total

and

ABC Cumulative Percent Of Total

#CumulativePercentOfTotal([Measures].[Data1 (Daily Sales Amount Jan 2014 to Sep 2014)])

• Returns cumulative percent of total for the designated measure item, in this case Daily Sales Amount Jan 2014 to Sep 2014 (this caption and the measure item name Data1 are part of the MDX syntax in the expression).

• Recommendation: Set the Format property to a percent and Total property to None.

and

#ABCCumulativePercent([Measures].[Data1 (Daily Sales Amount Jan 2014 to Sep 2014)],".65;.25")

• Assigns specified ranking values to results of the cumulative percent of total calculation, based on ranges specified in the expression. This expression assigns the following ranks: A for values >= 65%. B for values < 65% and >= 25%, and C for values < 25%.

• Recommendation: Set the Format property to a percent and Total property to None.

Cumulative Total

and

ABC Cumulative Total

 #CumulativeTotal([Measures].[Data2 (Budgeted Units Jan 14 to Sep 14)])

• Returns cumulative total for the designated measure item, in this case Budgeted Units Jan 14 to Sep 14 (this caption and the measure item name Data2 are part of the MDX syntax in the expression).

• Recommendation: Set the Format property to same format as measure item in the expression and Total property to None.

and

#ABCCumulative([Measures].[Data2 (Budgeted Units Jan 14 to Sep 14)],"75000000.00;35000000.00;10000000.00")

• Assigns specified ranking values to results of the cumulative total calculation, based on ranges specified in the expression. This expression assigns the following ranks: A for values >= 75,000,000; B for values < 75,000,000 and >= 35,0000,000; C for values < 35,000,000 and >= 10,000,000; and D for values < 10,000,000.

• Recommendation: Set the Format and Total properties  to None.

Type of Calculation & Function

Example Expression

Average

Uses Average numeric function.

Avg({[Measures].[Data1 (Sales Units Jan 2014 to Dec 2014)], [Measures].[Data2 (Sales Units Jan 2013 to Dec 2013)]})

·        Returns average sales units for 2013 and 2014. The expression syntax includes the names (Data1 and Data2) and captions of two regular measure items that are part of the view.

·         Recommendation: Set the Type property to Distinct Calculated.

Difference

[Measures].[Data22 (Act Gross Margin After Rebate)]-[Measures].[Data21 (Std Gross Margin After Rebate)]

Returns difference between the Act Gross Margin After Rebate and Std Gross Margin After Rebate measure items (their captions and the measure item names Data22 and Data21 are part of the MDX syntax in the expression).

Extended List Price

Uses attribute relationship and Val function.

This calculation determines the extended list price for the year to date by multiplying a measure item in a view by the Prod Current List Price attribute relationship from the Product level. It uses a Val function in its expression. Validations built into the calculation only allow it to proceed if the attribute relationship in it has numeric values and other specified items are active in the view at the time the calculation is performed. If any condition specified by a validation is not met, a null value is returned instead of performing the calculation.

The full expression follows. The text in green, bold font is the core calculation. Text preceded by a // mark are comments that explain each part of the expression. Comments are ignored by Viewer. A monetary format is recommended for this type of calculation.

Full Expression:

// Only do the calculation if the current level is Product AND

// if the Prod Current List Price attribute value is numeric AND

// if Daily Sales Units exist

IIF([Product].[Product].CurrentMember.Level.Name = "Product" AND

isnumeric([Product].[Product].Properties("Prod Current List Price")) = True AND

[Measures].[Data1 (Daily Sales Units)] <> null,

// Here’s the calculation

Val([Product].[Product].Properties("Prod Current List Price"))*[Measures].[Data2 (Daily Sales Units)],

// Return null value if the validations prevent doing calculation

// This syntax closes out the IIF validation

null)

The following image shows what the expression looks like in Viewer’s Expression window.

Here is the view when the Product level is visible. The calculation is performed.

Here is the view after some changes were made to the levels for it. Product level is no longer visible, so the calculation is not performed. Null values are returned.

Number of UPC’s Sold and Total Number of UPC’s

The first calculation counts the number of UPC’s that have sales for the level visible in the view. The second calculation counts the total number of UPC’s. The UPC level is evaluated against sales amount values for the current quarter. Validations built into each calculation only allow it to proceed if it meets certain conditions. If the condition specified by a validation is not met, a null value is returned instead of performing the calculation.

Each full expression follows. The text in green, bold font is the core calculation. Text preceded by a // mark are comments that explain each part of the expression. Comments are ignored by Viewer. For both calculations, Format and the Total properties are set to None.

Full Expression For Counting Only UPC’s With Sales:

// Only do the calculation for values where Sales Amount Current Qtr is not zero

IIF([Measures].[Data1 (Sales Amount Current Qtr)] <> 0,

// Here’s the calculation

// The EXCLUDEEMPTY tells it to not count UPC’s without sales

Count(CrossJoin({[UPC Global Number].[UPC Global Number].[UPC Global Number].members},{[Measures].[Data1 (Sales Amount Current Qtr)]}),EXCLUDEEMPTY),

// Return null value if the validation prevents doing calculation

// This syntax closes out the IIF validation

null)

Full Expression For Counting UPC’s With and Without Sales:

// Only do the calculation for values where Sales Amount Current Qtr is not zero

IIF([Measures].[Data1 (Sales Amount Current Qtr)] <> 0,

// Here’s the calculation

// The INCLUDEEMPTY tells it to count all UPC’s even those

// without sales

Count(CrossJoin({[UPC Global Number].[UPC Global Number].[UPC Global Number].members},{[Measures].[Data1 (Sales Amount Current Qtr)]}),INCLUDEEMPTY),

// Return null value if the validation prevents doing calculation

// This syntax closes out the IIF validation

null)

The following images shows what each expression looks like in Viewer’s Expression window. The last image shows the results in the view with the calculation results displayed in the marked columns.

Here is the view showing calculation results.

Profit (Sales after Costs)

and

Sales after Returns

Uses Absolute value of a measure item.

[Measures].[Data2 (Actual Sales Sales Amount)]-[Measures].[Data1 (Actual Sales Ext Standard Cost)]

and

[Measures].[Data2 (Actual Sales Sales Amount)]-abs([Measures].[Data4 (Actual Sales Sales Return Amount)])

• The first expression returns the profit, the total sales after costs. The syntax for the two measure items used in the calculation includes their captions and names (Data2 and Data1).

• The second expression returns the sales after returns. The syntax for the two measure items used in the calculation includes their names (Data2 and Data4) and captions. That part of the expression also uses the Abs function to use the absolute value of returns in the calculation.

Segment the Total Sales For a Level by Specific Members of a Different Level (see more detail)

Uses the SUM function, Members, and Tuples

This type of calculation lets you segment totals for levels in your view by members of other levels. For example, look at a Ship-To Market and Product Category sales by specific distribution channels. The following gives you the total sales within each Market/Category for only two specific distribution channels and ignores values for all other channels.

IIF([Measures].[Data1 (Total Sales Jan CY to Sep CY)]<>0,SUM({[Distribution Channel].[Distribution Channel].[Distribution Channel].[INB],[Distribution Channel].[Distribution Channel].[Distribution Channel].[DIR]},[Measures].[Data1 (Total Sales Jan CY to Sep CY)]),null)

• Two tuples are joined together by the SUM function in the expression, and that tells Viewer to retrieve sales values for those two specific distribution channels. This example sums the total sales for Indirect - Broker (INB) and Direct Sales (DIR) distribution channels.

• The IIF function in the expression checks that sales values exist before performing the calculation. The calculation is performed only if values exist, otherwise a null value (empty cell in your view grid) is returned by the calculation.

Return Text Value if Condition is Met

Uses IIF function to check for conditions and determine which results to return.

IIF([Measures].[Data22 (Actual Sales Sales Units Jan to Dec)]>[Measures].[Data2 (Budget Budget Units Frozen Jan to Dec)],"y",null)

• Uses the IIF function to set up an If/Then/Else scenario. If the specified condition is true, then the first specified value will be returned. Otherwise (else), a null value will be returned. In this case, the condition checked for is whether or not Actual Sales Sales Units are greater than Budget Budget Units Frozen. The calculation returns a "y" (for Yes) if the condition is true. If the condition is not true, the calculation returns a null value (empty cell).

• The syntax for the two measure items in both examples includes their names (Data22 and Data2) and captions.

• You can use a variety of values for the returned text, such as a letter or word, based on what best suits your view needs. In this case, null is recommended as the second (Else) value to prevent otherwise empty rows or columns from displaying. For example, if a row is hidden by relationship and empty filter because it has no sales or budget data, it would display if you set the second value in the expression to a 0 or "n" because those results would be considered a value by the relationship and empty filter. Using null as we did keeps results in an empty cell for such rows and therefore the rows will remain hidden. See also Using Relationship and Empty Filters.

• Recommendation: Set the Format property to None.

Standard Cost

Uses Val function and attribute relationship.

This calculation determines the standard cost for last year by multiplying a measure item in a view by the value of a Std Cost Last Year attribute relationship from the Product level. It uses a Val function in its expression. Validations built into the calculation only allow it to proceed if the attribute relationship in it has numeric values and other specified items are active in the view at the time the calculation is performed. If any condition specified by a validation is not met, a null value is returned instead of performing the calculation.

The full expression follows. The text in green, bold font is the core calculation. Text preceded by a // mark are comments that explain each part of the expression. Comments are ignored by Viewer. The Format property for the calculation is set to a monetary format and its Total property is set to None.

Full Expression:

// Only do the calculation if the current level is Product AND

// if the Std Cost Last Year attribute value is numeric AND

// if Actual Sales Units exist

IIF([Product].[Product].CurrentMember.Level.Name = "Product" AND

isnumeric([Product].[Product].Properties("Prod Std Cost Last Year")) = True AND

[Measures].[Data2 (Actual Sales Units)] <> null,

// Here’s the calculation

Val([Product].[Product].Properties("Prod Std Cost Last Year"))*[Measures].[Data2 (Actual Sales Units)],

// Return null value if the validations prevent doing calculation

// This syntax closes out the IIF validation

null)

The following image shows what the expression looks like in Viewer’s Expression window.

The next image shows the view when the Product level is visible. The calculation is performed because all calculation conditions were met including the condition that Product must be visible.

Here is the view when Ship-to Market has been drilled up to and Product is no longer visible. Viewer returns null values instead of performing the calculation because Product is not visible.

Cost Difference

Calculation uses values from a level’s attribute relationship (attributes).

This example uses a Val function and two Cost attribute relationships for UPC Global Number. The “Cost This Yr” and “Cost Last Yr” attributes are subtracted to find the difference in costs between this year and last year. Validations built into the calculation only allow it to proceed if the attribute relationships in it are numeric values and other specified items are active in the view at the time the calculation is performed. If any condition specified by a validation is not met, a null value is returned instead of performing the calculation.

The full expression follows. The text in green, bold font is the core calculation. Text preceded by a // mark are comments that explain each part of the expression. Comments are ignored by Viewer. The Format property for the calculation is set to a numeric format and its Total property is set to None.

Full Expression:

// Only do the calculation if current level is UPC Global number AND

// if the Cost This Yr AND Cost Last Year attribute values are

// numeric AND

// if Sales Units This Yr exists

IIF([UPC Global Number].[UPC Global Number].CurrentMember.Level.Name="UPC Global Number" AND

isnumeric([UPC Global Number].[UPC Global Number].Properties("Cost This Yr")) = true AND isnumeric([UPC Global Number].[UPC Global Number].Properties("Cost Last Yr")) =true AND

[Measures].[Data1 (Sales Units This Yr)] <> null,

// Here's the calculation

Val([UPC Global Number].[UPC Global Number].Properties("Cost This Yr")) - Val([UPC Global Number].[UPC Global Number].Properties("Cost Last Yr")),

// Return null value if the validations prevent doing calculation

// This syntax closes out the IIF validation

null)

The following image shows what the expression looks like in Viewer’s Expression window.

The next image shows results in the view when all calculation conditions were met and Viewer performed the calculation. The calculation results are in the last column.

This final image shows results in the view when one of the calculations conditions was not met. The UPC Global Number level is not visible in the view, so Viewer returned a null value instead of performing the calculation.

Truncate String Of Characters From Level Or Attribute Values

The LEFT and RIGHT functions are used in these calculations.

The following calculations return a string of ‘x’ number of characters from the values of other items in a view. The first calculation returns the first 4 characters of the UPC List Catalog Number attribute relationship from the UPC Global Number level. The second calculation returns the last 10 characters of the key values for that level.

Validations built into the calculation only allow it to proceed if the level is visible in the view. If that validation is not met, a null value is returned instead of performing the calculation.

The full expression follows. The text in green, bold font is the core calculation. Text preceded by a // mark are comments that explain each part of the expression. Comments are ignored by Viewer. Both calculations have their Format and Total properties set to None.

Full Expression Returning First 4 Characters For Attribute Values:

// Only do the calculation if current level is UPC Global number AND

// if Sales Amount Current Yr / Month exists

IIF([UPC Global Number].[UPC Global Number].CurrentMember.Level.Name="UPC Global Number" AND

[Measures].[Data1 (Sales Amount Current Yr / Month)] <> null,

// Here's the calculation

LEFT([UPC Global Number].[UPC Global Number].CurrentMember.Properties("UPC List Catalog Number"), 4),

// Return null value if the validations prevent doing calculation

// This syntax closes out the IIF validation

null)

Full Expression Returning Last 10 Characters For Level Values:

// Only do the calculation if current level is UPC Global number AND

// if Sales Amount Current Yr / Month exists

IIF([UPC Global Number].[UPC Global Number].CurrentMember.Level.Name="UPC Global Number" AND

[Measures].[Data1 (Sales Amount Current Yr / Month)] <> null,

// Here's the calculation

RIGHT([UPC Global Number].[UPC Global Number].currentMember.name, 10),

// Return null value if the validations prevent doing calculation

// This syntax closes out the IIF validation

null)

The following images shows what each expression looks like in Viewer’s Expression window. The last image shows the results in the view when the UPC Global Number level is visible in the view, which meets the calculation conditions and allows Viewer to move ahead with performing the calculation.

Results of the calculations are shown in the last two columns.

This variation on the view displays the truncated items (the level value and one of its attributes) to show you the difference between their original states versus their truncated states returned by the calculations.

Top N Total

and

Bottom N Total

Use Sum function.

Sum({TopCount([RepBroker].[RepBroker].[RepBroker].members, 4, [Measures].[Data2 (Daily Sales Units Current Yr Month)])},  [Measures].[Data2 (Daily Sales Units Current Yr Month)])

and

Sum({BottomCount([RepBroker].[RepBroker].[RepBroker].members, 4, [Measures].[Data2 (Daily Sales Units Current Yr Month)])},  [Measures].[Data2 (Daily Sales Units Current Yr Month)])

• The first calculation returns the total sales of the four RepBrokers with the highest sales. The portion of the expression enclosed in curly brackets { } and beginning with TopCount is what tells Stratum.Viewer to look for the four RepBroker members with the highest values for the specified measure item of Daily Sales Units Current Yr Month. The sum part of the expression is what totals the four values. The expression syntax includes the name of the RepBroker level, hierarchy, and dimension and includes the name (Data2) and caption of the measure item.

• The second calculation returns the total sales of the four RepBrokers with the lowest sales. The calculation is set up the same as the first calculation except it uses the BottomCount function.

• Recommendation: Set the Format property to same format as measure item in the expression and Total property to None.

Variance Percentage

Use the Percent of Change function when you want to include a variance percentage calculation in your view. That function is a Stratum.Viewer function that automatically includes a divide by zero check in the calculation to avoid divide by zero errors. See the first table in this topic for an example.

Days Until Expiration

Uses the Date Difference function, Today date function, and an attribute relationship.

This calculation shows us days until Lots expire. It uses the Date Difference function with a value of "d" to calculate the difference in days between the current date and Lot Expiration Date. A Today function provides the current date while one of the Lot level’s attribute relationships provides the expiration date. A validation built into the calculation only allows it to proceed if the Lot has a valid expiration date. If the condition is not met, a null value is returned instead of performing the calculation.

The full expression follows. The text in green, bold font is the core calculation. Text preceded by a // mark are comments that explain each part of the expression. Comments are ignored by Viewer. The Format and Total properties for the calculation are set to None.

Notes: This example calculates the "days" until expiration using the parameter of "d" in the Date Difference function. These other parameters can be used to calculate results in other intervals of time: yyyy for year, q for quarter, m for month, y for day of year, w for weekday, ww for week, h for hour, m for minute, and s for second.

Full Expression:

// Only do the calculation if the Lot Expiration Date for the Lot

// is a valid date

IIF(IsDate([Lot].[Lot].Properties("Lot Expiration Date")),

// Here's the calculation which uses Date Difference

// with a value of 'd' to determine how many days

// until a Lot expires or how many days ago it expired.

// The Today function which has syntax of Now()

// determines the current date for use in the calculation.

DateDiff("d", Now(), [Lot].[Lot].Properties("Lot Expiration Date")),

// Return null value if the validation prevents doing calculation

// This syntax closes out the IIF validation

null)

The following image shows what the expression looks like in Viewer’s Expression window.

The next image shows results in the view when the calculation condition was met and Viewer performed the calculation.

Notes: Results with negative numbers would indicate that a Lot already expired “x” days ago. For example, something that expired 5 days ago would have a -5 result. This example does not have any expired Lots.