Skip to main content

Use analytic functions

Use simple analytic functions

Create a custom formula function

A custom formula is a formula that is created for use with a specific resource. A custom formula is a basic function used for simple calculations such as addition, subtraction, division, and multiplication. Custom formulas can only be used within the resource in which they are created.

  1. Open Resource Explorer. Open Data Hub Training in Public.

  2. Click on Sales > Performance. Select a resource.

  3. Open the resource (e.g. sales report). On the Query panel, right-click the Rows or Columns placeholder. Choose the Custom Formula option.

  4. Drag items from the Dimension Tree to the Formula placeholder.

  5. Add operators and other symbols to the formula by right-clicking the Formula placeholder.

  6. Add a Name by clicking in the top left.

    1. Name - Give the custom formula a descriptive name. This name will appear on the Custom formula placeholder tab.

    2. Description - Add a description of the custom formula. This information is visible when you hover over the custom formula.

  7. Specify settings:

    1. Parent hierarchy - Specify where in the Dimension Tree the custom formula will be placed.

    2. Caption - use this to specify a dynamic caption that changes based on a specified member.

    3. Drillthrough override set - Override the filtering members.

    4. Calculation Order - Specify the calculation precedence of the formula.

  8. Click Apply.

Edit an existing custom formula function

  1. Click the custom formula placeholder tab on the Rows or Columns placeholder to open the Custom Formula design.

    CustomFomula.png

  2. Add and remove items:

    1. Add. Drag new entries from the DIMENSION TREE into a placeholder, then edit Formula or Parenthierarchy placeholders:

      1. Items can be dragged from the Rows or Columns placeholders; however, they will exist both on the original placeholder and within the custom formula.

    2. Remove by right-clicking items on the existing formula tab and select Remove

      image_10_custom_formulas_removing_rightclick.png

  3. Change formula settings (e.g. formula operators, formula name, description, and calculation order).

  4. Click Apply.

Create a custom set

Create custom sets by using the Custom Set function. Add items to the custom set by using the Custom Set dialog box using either of the following methods.

Method 1: Add items from the placeholders

  1. Select multiple items from the same placeholder using CTRL/Click.

    image_11_customsets_highlighted.png

  2. Right-click one of the selected items.

  3. Select Custom Set.

    image_12_-_customsets_rightclick.png

  4. The Custom Set design appears with selected items in the Set placeholder.

    image_13_-_customsets_setplaceholder.png

    1. Name. Give the custom set a descriptive name.

    2. Description. Include additional information regarding the custom set.

  5. Click Apply.

    image_14_-_customsets_newset.png

Method 2: Add items via right-clicking the columns or rows placeholder

  1. Right-click the placeholder that will contain the new custom set, and choose Custom Set.

    image_15_customsets_hoverbutton.png
    image_16_-_customsets_emptydialogbox.png

  2. Drag the items that you want to include in the custom set from the Dimension Tree to the Set placeholder.

    Image_17_-_customsets_dragging.png

  3. Add Name and Description.

  4. Click Apply.

Add additional items to a custom set

  1. Click the custom set placeholder tab to open the Custom Set design.

    image_19_-_customsets_placeholder_adding.png

  2. Drag items to the custom set from the DIMENSION TREE to the Set placeholder.

    image_21_-_customsets_adding.png

  3. Click Apply.

    IMAGE_22_-_CUSTOM_SET_customsets_adding_final.png

Remove items from a custom set

  1. Click the custom set placeholder tab to open the Custom Set design.

  2. Right-click the items you want to remove from the Set placeholder and choose Remove.

    24_-_customsets_removing_dragging.png

  3. Click Apply.

    image_25_customsets_removing_final.png

Unwrap a custom set

  1. Locate the custom set you want to unwrap in the Row or Column placeholder.

  2. Right-click the Custom set placeholder tab and select Unwrap default field: Set from the menu. The custom set is removed and is replaced on the placeholder by its contents.

    27_customsets_unwrap_final.png

Nest an analytic function

There are three different methods for nesting functions:

Tip

These examples will show how to nest a sum function.

Method 1: Creating a nested function within a new function

  1. Click the Functions Explorer and locate the Sum function entry in the Value folder.

    image_28_-_sum_entry.png

  2. Drag the Sum function to the Rows placeholder.

    29_-_rows_placeholder.png

  3. Right-click the Set placeholder in the Sum function dialog box.

  4. Hover over the Set option and click Head.

    image_30_-_dialog_box.png

  5. Define the Head function:

    1. In the DIMENSION TREE, navigate to the following location:

      1. Date dimension, Date.Calendar hierarchy.

    2. Drag the Month level to the Set placeholder in the Head function dialog box.

      1. A Month tab appears on the placeholder.

    3. On the Count placeholder, specify a placeholder number.

    4. Click outside of the field or press the ENTER key on your keyboard.

    5. Click Apply.

      Image_31_-_functions_nesting_m1_headtab.png

  6. In the Name text box, type Total (replacing the default "Sum" text).

  7. Click Apply.

    image_32_-_new_name.png

Method 2: Create a nested function by referencing an existing function tab

  1. Hover over the Row placeholder.

  2. Click the Function.

    image_33_-_function_set.png

  3. Hover over Value and click Sum.

    image_34_-_sum_option.png

  4. Click and drag the existing Head function to the Set field.

    image_35_-_set_input_field.png

  5. In the Name text box, type Total.

  6. Click Apply.

    image_37_-_new_name.png

Method 3: Create a nested function by right-dragging an existing function tab

  1. Right-click and drag the existing Head function tab to an open area of the Rows placeholder.

    image_38_-_highlighted.png

  2. Release the mouse button to reveal a menu showing all of the available functions.

    image_39_-_available_functions.png

  3. Hover over Value and click Sum.

    image_40_-_sum_option.png

  4. Click the Sum tab to reveal the function dialog box. The tab in the Sum function Set input field is labeled Reference to Head. Changes will be automatically reflected in the Sum function.

    image_43_-_reference_to_head.png

  5. Type Total in the Name text box (replacing the default Sum text).

  6. Click Apply.

    image_41_-_new_name.png

By type

Use a numeric-type function (Sum)

The sum function returns the sum of the specified set.

Tip

Example: The Sum function is used to find the sum of the total sales invoice amount for a Customer Group.

1 Sum_wrh.png

  1. Expand the Measures folder in the Dimension Tree.

  2. Expand the Sales invoice measure group.

  3. Drag the Sales invoice Amnt measure to the Columns placeholder. Sales invoice Amnt becomes a tab on the Columns placeholder.

  4. Go to the Rows placeholder. Right-click on it to open up a pop-up menu.

  5. Hover over Valuein the pop-up menu to display a submenu.

  6. Click Sum from the submenu. Sum becomes a tab on the Rows placeholder and the Sum function pop-up box opens.

  7. Collapse the Measures folder in the Dimension Tree to display the list of dimensions.

    • Expand the Customers dimension.

    • Expand the Customer Group Hierarchy.

  8. Drag the Customer Group level to the Sumfunction pop-up box and drop it on the Set placeholder. Customer Group becomes a tab on the Set placeholder.

  9. Click Apply .

The resulting analysis should show the Sum of the total sales invoice amount for Customer Group.

2 Sum_report_wrh.png

Use a set-type function (Top Count)

The set-type function returns a specified number of items from the top of a set.

Tip

Example: Top Count function is used to find the top ten most sold items.

1 top count example Top_Percent_Order_Line_Amount.png

  1. In the Dimension Tree, expand the Measures folder.

  2. Expand the Sales order lines measure group.

  3. Drag the Order line amount measure to the Columns placeholder. Order line amount becomes a tab on the Columns placeholder.

  4. In the Dimension Tree, collapse the Measures folder.

    • Expand the Sales order lines dimension.

    • Expand the Sales Status attribute.

    • Expand the Sales Status level.

    2 top count Top_Percent_Order_Line_Amount_Invoiced.png

  5. Drag the Invoiced member to the Filters placeholder. Invoiced becomes a tab on the Filters placeholder.

  6. Go to the Rows placeholder. Right-click on it. A pop-up menu opens.

  7. Hover over Set in the pop-up menu to display a submenu.

  8. Hover over Top and Bottom on the submenu to display an additional submenu.

  9. Click Top Count on the additional submenu. Top Count becomes a tab on the Rows placeholder. The TopCount function pop-up box opens.

2 top count Top_Percent_Order_Line_Amount_Invoiced.png
3 - TC Top_Count_wrh.png

  1. Expand the Items dimension in the Dimension Tree.

  2. Expand the Items attribute.

  3. Drag the Items level to the Top Count function pop-up box and drop it in the Set placeholder. Items become a tab on the Set placeholder.

  4. Drag the Order line amount tab from the Columns placeholder to the Top Count function pop-up box and drop it in the Formula to the order by the placeholder. Order line amount becomes a tab on the Order by the placeholder.

  5. Click on the Count placeholder. An open value field displays.

  6. Type 10 into the open value field and press the Enter key on your keyboard. The number 10 becomes a tab on the Count placeholder.

  7. Click Apply .

  • The analysis should return the top ten most sold items.

4 - TC final Top_Count_report_wrh.png

Use a member-type function (Parent)

The parent function returns the parent of the specified member.

Example: The parent function is used to find the total sales invoice amount for a specific quarter.

1 EXAMPLE Parent_wrh.png

  1. In the Dimension Tree, expand the Measures folder.

  2. Expand the Sales invoice measure group.

  3. Drag the Sales invoice Amnt measure to the Columns placeholder. Sales invoice Amnt becomes a tab on the Columns placeholder.

  4. Go to the Rows placeholder. Right-click on it. A pop-up menu opens.

  5. In the pop-up menu, hover over Member to reveal a submenu.

  6. From the submenu, click Parent. Parent becomes a tab on the Rows placeholder. The Parent function pop-up box opens.

  7. In the Dimension Tree, collapse the Measures folder. The list of dimensions displays.

    • Expand the Date dimension.

    • Expand the Date.Calendar hierarchy.

    • Expand the Calendar Month level.

  8. Locate the Jun 2010 member. If needed, then use the Search feature provided in the level.

  9. Drag the Jun 2010 member to the Parent function pop-up box. Drop it on the Member placeholder. Jun 2010 becomes a tab on the Member placeholder.

  10. Click Apply .

    The analysis should display two columns: Date.Calendar and Sales invoice Amnt. The table displays the sales invoice amount for the whole quarter of 2010. (The quarter for which June 2010 resides.)

2 Parent final.png

Use a selection type function (Children)

This function returns the specified member's children.

Example: Use the children function to find the total sales invoice amount for all children in the customer group hierarchy.

image 1 -example children Descendants_Member_wrh.png

  1. In the Dimension Tree, expand the Measures folder.

  2. Expand the Sales invoice measure group.

  3. Drag the Sales invoice Amnt measure to the Columns placeholder. Sales invoice Amnt becomes a tab on the Columns placeholder.

  4. In the Dimension Tree, collapse the Measures folder to display the list of dimensions.

  5. Expand the Customers dimension.

  6. Drag the Customer Group Hierarchy hierarchy to the Rows placeholder. Customer Group Hierarchy becomes a tab on the Rows placeholder.

  7. Right-click on the Customer Group Hierarchy tab. Do not click on the Rows placeholder. A pop-up menu opens.

  8. In the pop-up menu, hover over Selection Types to reveal a submenu.

  9. Click Children.

    The Customer Group Hierarchy tab becomes a function on the Rows placeholder. (As indicated by the function icon on the tab.)

    The function pop-up box does not automatically open if the function parameters have been met.

    The resulting analysis should return a two-column table that displays the total sales invoice amount.

  10. Click the plus sign to expand an entry to display its Children.

IMAGE 2 - CHILDREN FINAL Children_report_wrh.png

Use a relative time function (Context Period)

The context period function returns the period in context, either provided by the opposite axis or by the filter context.

Example: The context period function is used to total the Order line amount for all dates.

1 context example Context_Period_wrh.png

  1. Expand the Measures folder in the Dimension Tree.

  2. Expand the Sales Order lines measure group.

  3. Drag the Order line amount measure to the Columns placeholder. Order line amount becomes a tab on the Columns placeholder.

  4. Collapse the Measures folder in the Dimension Treeto display the list of dimensions.

  5. Expand the Date dimension.

  6. Drag the Date.Calendar hierarchy to the Rows placeholder. Date.Calendar becomes a tab on the Rows placeholder.

  7. Right-click in the open space below the Date.Calendar tab to open a pop-up menu.

  8. Hover over Time in the pop-up menu to display a submenu.

  9. Hover over Current Period from the submenu to display an additional submenu.

  10. Click Context Period from the additional submenu. Context Period becomes a tab on the Rows placeholder. The Context Period function pop-up box opens.

    Do not drag items into the Context Period function pop-up box

  11. Click Apply.

The analysis should return three columns: An expandable calendar, the current delivery date and the total order line amount for all dates.

2 Context final _Period_report_wrh.png

Use a relative time function (Year to Date)

The Year to Date Total (YTD) function returns the year-to-date total for the current time member.

Example: The Year to Date Total function is used to determine the total sales invoiced since a specified year.

image 1 YTD example Year_to_Date_Total_wrh.png

  1. Expand the Measures folder in the Dimension Tree.

  2. Expand the Sales invoice measure group.

  3. Drag the Sales invoice Amnt measure to the Columns placeholder. Sales invoice Amnt becomes a tab on the Columns placeholder.

  4. Right-click the Rows placeholder to reveal a pop-up menu.

  5. Hover over Timein the pop-up menu, to display a submenu.

  6. Hover over Period to Date Totals from the submenu to display an additional submenu.

  7. Click Year to Date Total (YTD) from the additional submenu. YTD becomes a tab on the Rows placeholder. The Year to Date Total (YTD) function pop-up box opens.

  8. Collapse the Measures folder in the DimensionTree to display the list of dimensions.

    • Expand the Date dimension.

    • Expand the Date.Calendar hierarchy.

    • Expand the Calendar Year level.

  9. Drag the 2010 member to the Year to Date Total (YTD) function pop-up box. Drop it on the Time Member placeholder. 2010 becomes a tab on the Time Member placeholder.

  10. Click in the Name field. Clear the field and type in 2010 QTD.

  11. Click the Apply button.

  12. The YTD tab on the Rows placeholder is renamed to 2010 YTD.

  • The resulting analysis shows the total sales invoiced since the year 2010.

image 2 - YTD final Year_to_Date_Total_Report_wrh.png

Use a logical function (Is Current Member)

This function returns the current member from a hierarchy.

Example: Current Member function is used to find total General Ledger (GL) Quantity of the All calendar months and show the GL quantity of the previous sixth calendar months.

  1. Expand the Date dimension in the Dimension Tree.

  2. Expand the Date.Calendar hierarchy.

  3. Drag the Calendar Month level to the Rows placeholder.

Image 1 - Current example Member_1_wrh.png

  1. Expand the Measures folder in the DimensionTree.

  2. Expand the General Ledger actuals measure group.

  3. Drag the GL Quantity measure to the Columns placeholder. GL Quantity becomes a tab on the Columns placeholder.

  4. Right-click in the open space to the right of the GL Quantity tab to open up a pop-up menu.

  5. Click Custom Formula in the pop-up menu. Custom Formula becomes a tab on the Rows placeholder. The Custom Formula function pop-up box opens.

  6. Drag the GL Quantity tab from the Columns placeholder to the Custom Formula function pop-up box and drop it in the Formula placeholder. GL Quantity becomes a tab on the Formula placeholder.

  7. Clear the contents of the Name field. Type in Parallel Month, Previous Semester.

Image 2 - CM Current_Member_2_wrh.png

  1. Right-click in the open space to the right of the GL Quantity tab on the Formula placeholder to open a pop-up menu.

  2. Hover over Member in the pop-up menu to display a submenu.

  3. Click Lag/Advance from the submenu. Lag/Advance becomes the second tab on the Formula placeholder.

    • The Lag/Advance function area opens up within the Custom Formula function pop-up box.

  4. Type in a value of 6 in the Lag field.

  5. Collapse the Measures folder in the Dimension Tree to display the list of dimensions.

  6. Drag the Date.Calendar hierarchy to the Lag/Advance function pop-up box and drop it on the Member placeholder. Date.Calendar becomes a tab on the Member placeholder.

Image 3 - CM Current_Member_3_wrh.png

  1. Right-click on the Date.Calendar. Do not click on the Member placeholder. A pop-up menu opens.

  2. Hover over Member in the pop-up menu to display a submenu.

  3. Click Current Member from the submenu.

    Date.Calendar becomes a function on the Member placeholder (as indicated by the function icon on the tab). The function pop-up box does not automatically open when the parameters for the function have been met.

    Image 4 - CM Current_Member_4_wrh.png

    The Lag/Advance function area collapses within the Custom Formula function pop-up box.

  4. Click Apply.

  5. Click Apply.

The Lag/Advance function area collapses within the Custom Formula function pop-up box.

The analysis should display the total general ledger quantity of all calendar months in the system along with the parallel months of the previous semester.

Image 5 - CM Current_Member_report_wrh.png

Number input parameterization

This function is a parameterized numeric entry that prompts the user with a text box when the resource is opened.

Example: Create an Input box to select numbers from three to ten, using the Number Input Function and with a three as the default selection.

image 1 - number example Number_Input_wrh.png

  1. Right-click on the Rows placeholder to open a pop-up menu.

  2. Hover over Parameterization in the pop-up menu to display a submenu.

  3. Click Number Input from the submenu, to create a Number Input function tab on the Rows placeholder.

  4. In the Number Input function pop-up box:

    • Click in the Minimum field. Enter a number minimum.

    • Click in the Maximum field. Enter a number maximum.

    • Click in the Value field. Enter a number that will be designated as the default value.

  5. Click the down arrow in the Display as field.

  6. From the drop-down menu, click Spin Box.

  7. Click Apply.

The resulting analysis displays a Number Input spinner box with a default value.

Image 2 - number Number_Input_report_wrh.png

Use a text function (Member Name)

This function enables the entry of text into a formula.

Example: Find a specific measure using the Text function to enable a text value to be returned.

Image 1 - Text_wrh.png

  1. Right-click on the Rows placeholder to reveal a pop-up menu.

  2. Hover over Text to display a submenu.

  3. Click Text from the submenu to create a tab on the Rows placeholder.

  4. Go to the Text field in the Text function pop-up box and type in the relevant text.

  5. Click the Apply button.

    • The analysis displays a two-column heading row that features the relevant text.

  6. Expand the Measures folder in the Dimension Tree and expand the required measure group.

  7. Drag the applicable measure to the Rows placeholder. The measure becomes a tab on the Rows placeholder.

Image 2 Text_report_wrh.png
In this section: