Skip to main content

Adding a Calculate Layer to a Widget

Updated

This article shows how to add a calculate layer to a widget. The calculate layer is used in grid-type widgets when users want to display a column that computes a value using the other columns in the widget. 

To add a calculate layer in a widget:

  1. Go to Widgets on the MSPbots menu. 
  2. Click on the New Widget button.

    new widget 
  3. Click Grid on the New Widget window. You will be redirected to the Widget Builder. 

    grid type widget

  4. Provide the necessary info for this widget like the Name, Description, and Role, then click Apply.

    widget builder
  5. Click the Dataset icon, then click + button for Data Source.

    widget builder dataset
  6. Select New Layer on the Add New Layer window. Note that you must create the columns and measure before you can compute for the calculate layer. 


    new layer widget
  7. In the Dataset window,
    1. Select the dataset with the data you need. 
    2. (Optional) Enter a datasource name in the Show datasource name as field. 
    3. For Columns Display
      1. Go to the Column Name dropdown and select a field for the drill-through. 


         
      2. Give it an Alias and select a Business Type for your selection. 
      3. Click the + button to add more rows.
         
  8. (Optional) Configuration for Filter
    1. Click the + icon and choose either Add Condition or Add Group. 
    2. Select all the fields you want to filter.
    3. Set conditions for each field using the two subsequent dropdown lists. For a guide on each option on the list, refer to the article What Filter Conditions and Formats are Available for Creating Widgets.
    4. Select the logical operator AND or OR for the filter group. 
  9. Configuration for Measure
    1. Click the + button corresponding to Measure. 

      image add measure
    2. When the Measure window opens, fill in the following fields:
      1. Summarize Type - Select calculation methods.
        • sum - Calculate the sum, for example, given a set of log time data: 8, 7.5, 9, 7.5, 9.5. The sum of log time values is 42.5.
        • count - Count the occurrences, for example, counting the log time occurrences results in 5.
        • avg - Calculate the average, for example, the average log time value is 8.5.
        • max - Find the maximum value, for example, the maximum log time value is 9.5.
        • min - Find the minimum value, for example, the minimum log time value is 7.5.
        • string_agg - List all data, for example, listing all log time values: 8, 7.5, 9, 7.5, 9.5.
      2. (Optional) Separator - When the Summarize Type is string_agg, you can set the separator for the listed data, such as a comma ",".
      3. Fields - Select a calculation option. The options in the dropdown menu are values set as Alias in Column Display.
        • When Summarize Type is set to sum, avg, max, or min, we recommend selecting a field with Business Type set to Number in Step 7.c.
        • When Summarize Type is set to count or string_agg, you can select any field with Business Type set in Step 7.c.
      4. Alias - Input a name for the metrics, automatically generated based on your choice of summary and fields.
      5. (Optional) Format type - Select the data type for Measure, usually corresponding to the Business Type set in Step 7.c.
        • Text
        • Number
        • Date
        • Date Time
        • Yes/No
        • User
        • If the default format does not meet your needs, you need to set the data format in advanced settings. Please click to configure.
      6. Distinct - If this option is selected, duplicates will be automatically filtered out during calculations, considering unique data.
    3. Click Add. 

      String.jpg
  10. Repeat Step 9 until you have all the measures for computing the Calculate Layer.

    widget calculate layer
  11. Click Save.
  12. Click the + button and choose Calculate Layer to calculate the data of Measure.

    add calculate layer

     calculate layer
  13. On the Calculate Layer window, fill in the following fields to set up the calculate layer. 
    • Name - Enter a unique name for the Calculate Layer.

      Remember that the name of the Calculate Layer should not overlap with the Alias field of Measure. If they overlap, the settings of the calculation layer will not take effect.

    • For Formula,

      1. Please select the measure to be used for the formula.

        When the Measure's Summarize Type is set to string_agg, do not add this measure to Formula. This is because the purpose of "string_agg" is to list data, and if set as a calculation layer, the data for this Measure will not display correctly.

      2. Input the formula in the box for the calculation layer.

        • For example, if you want to add 8 to the value of a certain Measure, you can click "+Measure," enter "+8" after "coalesce("Measure",0)" in the input box, and the formula will be "coalesce("Measure",0)+8."

          Calculate Layer.jpg
    • (Optional) Format Type - Select the data type of the calculation layer.
        • Text
        • Number
        • Date
        • Date Time
        • Yes/No
        • User
        • If the default format does not meet your needs, you need to set the data format in advanced settings. Please click to configure.

          format formula number
  14. Once done, click Save then Apply. The Calculate Layer will now appear. The widget displays the values of the Measure and the values of the Calculate Layer.

    If you want to create multiple calculation layers, repeat Steps 12, 13, and 14 to perform calculations on different data for Measures.

    billable percent

Related articles