Miscellaneous functions

Examples

Example usage with lists as dimensions

In this example, a Sales Commission Rates module has line items on rows and the Sales Personnel list on columns. The Personnel Code line item has a text data type.

The formula uses the ITEM function to identify the list item in the Sales Personnel list, and the CODE function to return the code of that list item.

Example usage with list-formatted line items

In this example, an Income Statement module has line items on rows, time on columns, and the Regions list on pages. The Personnel Code line item has a text data type. The Sales Staff line item has a list data type on the Sales Personnel list.

The formula uses the CODE function to return the code of the corresponding list item in Sales Staff.

Examples

General example

The COLLECT function can be used to collect values from a line item subset. In this example, the two source modules for the line item subset are named P&L and Fixed assets. The aggregated total for FY22 from the Time dimension displays as the single column in both modules, and line items on rows.

P&L module:

Revenue module:

The line items selected for the line item subset display as below within Line item subsets . The line item subset is named Receipts and Payments.

The above line item subset is used for the rows dimension in a Receipts and Payments module. FY22 from the Time dimension is on columns, and a single line item is on the pages dimension. The line item contains this formula: COLLECT(). This collects the values from the P&L and Fixed Assets source modules:

Line item subset example

The Variance report staging module step of the Line item subset example contains an example of how you can use the COLLECT function.

Examples

In this example, an income statement module has line items on rows, the Products list on columns, and versions on pages. The Budget version displays as a page selector.

The model that contains the module has three versions: Budget, Actual, and Forecast. The Actual version is set as Current.

The formula uses the CURRENTVERSION function to show the difference between budget and actual data for Cost of Goods.

Examples

These examples refer to a Products list. The Products list contains the following items, as seen in the Grid View tab in General Lists.

List example

In this example, time is on columns, and there are three line items on rows.

The top-selling product for each month is manually entered into the Top-selling product line item on a dashboard. The Fruit product top seller? and Vegetable product top seller? line items use the FINDITEM function to return the top-selling product if it is a fruit or vegetable respectively.

List with codes example

This example demonstrates how codes for list items can be used to find items, instead of the text in list item names.

Examples

Simple example

In this example, Time is on columns and a Products list is on rows. The page dimension contains two line items, which contain the formulas ITEM(Time) and ITEM(Products).

The table below displays the result of the ITEM(Time) formula.

The table below displays the result of the ITEM(Products) formula.

The ITEM function with IF THEN ELSE

You can use the ITEM function with the IF THEN ELSE function to create conditional formulas that change depending upon the applicable list item.

Take for example a module dimensioned by two lists named Products and Organization. If you require a calculation for a specific product, or products, you can use the formulas as below:

IF ITEM(Products) = Products.Product 1 THEN (Calculation for Product 1) ELSE (Calculations for other products)

IF ITEM(Products) = Products.Product 1 OR Products.Product 2 THEN (Calculation for Product 1 or Product 2) ELSE (Calculations for other products)

Additionally, you can specify a calculation that only applies to a specific combination of the Product and Organization lists by using the AND operator:

IF ITEM(Products) = Products.Product 1 AND ITEM(Organization) = Organization.Company 1 THEN (Calculation for Product 1 and Company 1) ELSE (Calculations for other products and companies)

Example with list

In this example, the Organization list is on columns, and the Class line item on rows. The Class line item has the list data type, and references the Company Class list. The Class line item uses the ITEM function to assign certain items in the Organization list a different value from the Company Class list. The formula used is:

IF ITEM(Organization) = Organization.'Company 01' OR Organization.'Company 05' THEN Company Class.Class A ELSE IF ITEM(Organization) = Organization.'Company 08' THEN Company Class.Class C ELSE Company Class.Class B

Example with time

In this example, time displays on columns, and the Product Release Status line item on rows. The Product Release Status line item has the list data type, and references the Product Status list. The Product Release Status line uses the ITEM function to assign different time periods a different value from the Product Status list, based on time period. The formula used is:

IF ITEM(Time) < TIME.'Apr 21' THEN Product Status.Before Release ELSE Product Status.After Release

Examples

In this example, an income statement module has line items on rows, time on columns, and versions on pages. The model has three versions: Budget, Actual, and Forecast, in that order. The page selector is set to the Actual version.

The formula in the Forecast Revenue Costs line item uses the NEXTVERSION function to retrieve the value of Revenue Costs from the Forecast version, which is the next version.

Examples

Example usage with a list

In this example, there are two lists, Outlets and its parent list Territories. The relationship of these lists is shown in the table below as they display in General Lists.

In the table below, three columns are line items and the rows are items from the Outlets list.

The Parent Territory line item has the list data type, and the Territories list is selected. The line item uses a formula that contains the PARENT and ITEM functions with the Outlets list. The ITEM function returns the list item for the Outlets list, and the PARENT function returns the parent of that list item, which is the territory.

The formula returns a blank result for the New Outlet 1 and New Outlet 2 list items as they do not have a parent.

Example usage with time periods

In the table below, the rows are line items and the columns are items from the Project Dates list.

The formula used in the Parent Quarter line item uses the PARENT function to return the parent of each month in the Month Period line item. For time periods, quarters are the parent of months, so the formula returns the quarter that the month belongs to.

Examples

In this example, an income statement module has line items on rows, time on columns, and versions on pages. The model has three versions: Budget, Actual, and Forecast, in that order. The page selector is set to the Actual version.

The formula in the Budget Cost of Goods line item uses the PREVIOUSVERSION function to retrieve the value of Cost of Goods from the previous version. 

Examples

Example of RANK with only Source values argument

This example contains an Organization list on columns, where cities are children of regions. There are two line items on rows, one for Sales, and one for the formula that contains the RANK function.

The cities are ranked by their value for the Sales line item. As the parent regions are summaries, they omit from the ranking.

As the formula only contains the Source values argument, the default keywords are used for the optional Direction and Equal value behavior arguments.

The default DESCENDING keyword is used for the Direction argument. This means the city with the largest value for the Sales line item ranks the highest.

The default MINIMUM keyword is used for the Equal value behavior argument. This means that Paris and Lyon, which both have the same value for the Sales line item, are both assigned the lower ranking. In this case, 4.

Direction argument examples

This example shows the behavior of the two keywords for the Direction argument.

The result of the DESCENDING keyword is that the largest value for the Sales line item ranks as 1, through to the smallest value, which ranks as 8.

The result of the ASCENDING keyword is that the smallest number for the Sales line item ranks as 1, through to the largest value, which ranks as 8.

Equal value behavior argument examples

This example shows the behaviors of the four keywords for the Equal value behavior argument. The values for the Sales line item for Paris and Lyon are both the same, 14,000, so they highlight the different behaviors:

• The MINIMUM keyword assigns both Paris and Lyon the numerically lower rank, 4.
• The MAXIMUM keyword assigns both Paris and Lyon the numerically higher rank, 5.
• The AVERAGE keyword assigns both Paris and Lyon the average of their ranks, 4.5.
• The SEQUENTIAL keyword assigns both Paris and Lyon ranks in the order of the underlying list, regardless of the parent hierarchy.

Include value argument examples

The Include value argument enables you to use a Boolean statement to determine whether a value is ranked. This can be a reference to a Boolean-formatted line item, or a Boolean statement entered directly into the formula.

In the first two formulas, the values to include in the ranking are determined by Boolean statements that specify they must either be:

• Larger than 10,000 and smaller than 24,000.
• Larger than 10,000.

The third formula contains a reference to the Include in Ranking? Boolean-formatted line item. As a result, it only ranks items in the Organization list, which the Include in Ranking? line item has a value of TRUE for.

Ranking groups argument examples

The Ranking groups argument enables you to specify criteria so that data ranks within sub-groups. The four example formulas in the table above demonstrate the following types of data used for the Ranking groups argument:

• The parent item of each territory within the Organization list (retrieved with a combination of the PARENT and ITEM functions). This means that territories rank among their peers within each region. For example, London and Birmingham for the UK, Paris and Lyon for France, and so on.
• A list-formatted Store Type line item that contains the type of store for each territory within the Organization list. Territories rank within groups for each store type.
•  The Opening date for each territory. If territories share their opening date, they rank among one another.
• The Opening date for each territory used with the YEAR function to return the year that date's in. Territories with an opening date in the same year rank among one another.

Time period- and date-formatted line item examples

You can also use a date- or time period-formatted value for the Source values argument. If you do this, values rank in chronological order.

If you use the default DESCENDING keyword for the Direction argument, the most recent value ranks as 1, the second most recent value tanks as 2, and so on.

If you use the ASCENDING keyword for the Direction argument, the oldest value ranks as 1, the second oldest value ranks as 2, and so on.

A blank value for a date- or time period-formatted value is ranked as old as possible by the RANK function. As such, in this example, the RANK function ranks the blank value as 8 for the DESCENDING keyword, and 1 for the ASCENDING keyword.

Examples

General examples

In this example, a module that contains the Salespersons list is on columns, and a number of line items is on rows.

The example uses RANKCUMULATE to cumulatively sum sales in the order of each salesperson's length of service. Further iterative formulas use the Include value and Ranking groups arguments to:

• Determine which salesperson's sales to include in the cumulative ranking.
• Further break down the cumulative ranking by region.

Two line items use the RANK function to help you identify the order that RANKCUMULATE cumulates values in.

Examples

Basic example of VALUE function

This example has a list on columns that contains items with descriptive names for the values they contain. On rows, there are two line items:

• Text value, which contains the text values for the Value to convert argument.
• Number value, which uses the VALUE function to convert these text values to number values.
  This formula also highlights how the VALUE function interacts with numbers that contain non-numeric characters.

Use VALUE with the MID function

Some functions, such as MID, return a text-formatted result. If these results are numeric, you can use the VALUE function to convert them to number-formatted.

For example, you can use the MID function to extract all characters after the first character. This enables you to remove currency symbols from numbers stored as text. The result can then be used with the VALUE function.

In this example the Revenue as text line item is text, and contains a value for several different currencies. The Revenue as number line item uses the MID and VALUE functions to convert the text that contains a number to numbers.