Using Parameterized Calculations in Columns

Parameterized calculations use QuickView values in the calculation. They allow you to change the source that is used to calculate the summary column.

If you have a tabular display that uses a common calculation in multiple summary columns, you can use a QuickView in a parameterized calculation to apply different summary data to the calculation instead of defining each column individually.

How it works

Example 1: Shows a simple table with columns for Cost and Revenue. It uses the following definitions for the columns:

Total[Cost]

Total[Revenue]

 

If you create a list QuickView with Cost and Revenue values, you can use that QuickView to define a new column. In this example, the QuickView name is Orders.

Total[$(Orders)]

 

This is called a parameterized calc because the calculation is based on a variable element.

You can replace the two original columns with the single parameterized calc column. When you change the QuickView value, the column updates to the selected value.

TIPS:

• When creating the parameterized calculation column, remember to use a unique column name.
• Enter the description before you enter a name. Otherwise, when you enter the definition value, the name that you entered is overwritten by the definition.
• Use the Label is different from name option, and enter the QuickView variable as a label. This ensures that the column label matches the selected QuickView value.

How it's done

Example 2: Shows a larger table with eight columns for sales orders. It uses time series and the % variance function to compare the cost and revenue from this year-to-date, to the cost and revenue from last year-to-date.

This example uses the following definitions for the columns:

Total[Cost, Order Date="ts Period Comparison p YTD"]

Total[Cost, Order Date="ts Period Comparison p Last YTD"]

calc[Cost YTD]- calc [Cost LYTD]

pct_var(calc[Cost YTD], calc [Cost LYTD])

Total[Revenue, Order Date="ts Period Comparison p YTD"]

Total[Revenue, Order Date="ts Period Comparison p Last YTD"]

calc[Revenue YTD]- calc [Revenue LYTD]

pct_var(calc[Revenue YTD], calc [Revenue LYTD])

 

Parameterized calculations can be used to consolidate columns and reduce the display to four columns.

To do this:

1. Open the cBase.

2. Create a list QuickView with the values that you want to use in the columns. In this example, the QuickView name is Orders and the values are Cost and Revenue.

   1. On the toolbar, select Data > Add QuickViews.

      The Edit QuickViews dialog box opens.

   2. Select Add List QuickViews.

      The Edit List QuickView dialog box opens.

   3. Enter a Name for the QuickView.

   4. Select the List Values option, enter values for the QuickView, and then click OK.

      

   5. Close the Edit QuickView dialog box.

      The Orders QuickView is added to the Dive window.

3. Create the parameterized column:

   1. On the toolbar, select Data > Edit Columns.

      The Edit Columns dialog box opens.

   2. Click Add.

      The Add Column dialog box opens.

   3. Define the column using the QuickView instead of the dimension name. For example:

      Total [$(Orders),Order Date="ts Period Comparison p YTD"]

   4. Enter a unique name for the column.

   5. Select Label is different from name, and enter a name for the label using the QuickView. For example:

      Billed $(Orders) YTD

      TIP: Using the QuickView as a variable enables the column label to change dynamically, depending on the selected QuickView value.

      

   6. Click OK to close the dialog box.

   7. The parameterized column is added to the Available Column list.

4. Repeat step 3 for the other columns that you want to parameterize.
5. If necessary, remove the columns that are being replaced.