Building a gauge component that displays a cumulative total

The gauge component allows you to make a comparison between two related values: the current value and the target value. In this example, you can see at a glance how actual sales compare to budgeted monthly sales.

To create a more dynamic target value, one that compares YTD monthly values for both actual and budgeted sales, a cumulative sum on a range of cells is performed. The formula includes empty cells so that when next month's sales figure is added to the data source the Klip is immediately updated.

 

You can click the following link to download the simplified training data source file.

https://static.klipfolio.com/images/saas/ACME_simplified_sales.xlsx

Step 1: Building and configuring the gauge component

  1. Click the Build a Klip button.
  2. Select the Gauge component.
  3. Select Use an existing data source and select our sample data set.
  4. Select the Klip component and in the Properties panel, enter a name for the Klip Title. For example, YTD Sales Performance.

Location

Action

For our example

With the Current value sub-component selected, select the Data panel Select the Insert data from a data source button and select the cell that contains total actual sales for the YTD (cell O3), overwriting the default value 50. 431
With the Properties panel selected Select the following properties:
Format as: Currency
Suffix: K
Prefix: YTD: (space)
Font size: Large
Font style: Bold
Font Color: Blue
YTD: $431K
With the Min value sub-component selected Select the Data panel and leave the existing base value of 0. 0
With the Max value sub-component selected Select the Data panel and delete the initial default value of 100 using the trash can. Select the cell that contains the total budgeted sales for the YTD (cell O3) 1,556
With the Properties panel selected Select the following:
Format as: Currency
Suffix: K
Prefix: Goal: (space)
Font size: Medium
Font style: Italics
Font Color: Black
Goal: $1,556K
With the Target value sub-component selected Select the Data panel and delete the initial default value of 80 using the trash can. For budgeted total sales, highlight the cells for all the months in the row. C3:N3
With C3:N3 selected Select Wrap current value in function, SUM SUM( C3:N3 , data )
With C3:N3 selected Select Wrap current value in function, Data Manipulation, SLICE SUM( SLICE( C3:N3 , start , end ) data )
With start selected type 0, and click Apply SUM( SLICE( C3:N3 , 0, end ) data
With end selected click Insert function, Math, COUNT SUM( SLICE( C3:N3 , 0 , COUNT( data ) ) )
With data selected For actual total sales, highlight the cells for all the months in the row, including empty cells. SUM( SLICE( C3:N3 , 0 , COUNT( C2:N2 ) ) ) 
  Click SUM to highlight the whole function. Click Evaluate current selection to verify the result. 511
With the Properties panel selected. Select the following:
Format as: Currency
Suffix: K
Prefix: Target YTD: (space)
Font size: Small
Font style: Regular
Font Color: Black
 

Step 2: Setting up chart indicators

  1. Select the Gauge component.
  2. Select the Indicators panel.
  3. Select Add one now.
  4. Add the following condition: If the current value (Actual YTD) is less than the target value, change the color to red.
  5. Select Add.
  6. Add the following condition: If the current value (Actual YTD) is greater than the target value, change the color to green.
  7. For more information on indicators, see How to use Indicators.

Step 3: Selecting a gauge style

  1. Select the Gauge component.
  2. Select the Properties panel.
  3. For Gauge Style, select Arc.
  4. Optional: For Size, select Small.
  5. For more information on how to add ranges, see How to build Gauge components.

Step 4: Troubleshooting tip

  • Periodically, resize the gauge in the preview window to have all values display.

Feedback and Knowledge Base