Adding a drop down list to a Gauge component

This example Klip shows you how to add a drop down list to a Gauge component.  The drop-down list allows the user to select a month and display an associated value using the Current Value sub-component of the Gauge component. The Target Value sub-component of the Gauge component displays the total predicted values for the year 2013.

 

To do this, you have to:

  1. Build a Gauge component
  2. Add a User Input Control component
  3. Create GROUP, GROUPBY and SELECT statements

This Klip uses the following data set.

  A B C
1 Date Value Predicted Value
2 1-Jun-12 100,000 100,000
3 1-Jul-12 100,000 150,000
4 1-Aug-12 150,000 200,000
5 1-Sep-12 175,000 200,000
6 1-Oct-12 200,000 250,000
7 1-Nov-12 175,000 250,000
8 1-Dec-12 200,000 250,000
9 1-Jan-13 175,000 250,000
10 1-Feb-13 200,000 300,000
11 1-Mar-13 225,000 275,000
12 1-Apr-13 250,000 275,000
13 1-May-13 225,000 300,000
14 1-Jun-13 300,000 300,000

Step 1: Building the Gauge component

  1. Click Klips in the left navigation bar.
  2. Click Build a Custom Klip.
  3. Drag in the Gauge component.
  4. Click +Add Data Source and, on the Add a Data Source page, select your data source.
  5. From the component tree, select the Klip component.
  6. From the Properties tab, type a name for the Klip Title.

Note: For our example, Gauge with User Input Control Component

Step 2: Adding and configuring the User Input Control component

  1. Drag in a User Input Control component above or below the Gauge component.  The default is Drop-Down List.
  2. From the Properties tab, enter the following information.
    • Control Label, eg Select a month
    • Create New Variable, eg month
    • Default Value, eg 0112
    • Scope, e.g. Only this Dashboard
  3. From the Drop-Down List component, select the Values sub-component.

GROUP( DATE_CONVERT( SLICE( A:A ) , "d-MMM-yy" , "yyMM" ) ) ...

Evaluates to: 1206, 1207, 1208, 1209, 1210, 1211, 1212, 1301, 1302, 1303, 1304, 1305, 1306. 

  1. From the Drop-Down List component, select the Labels sub-component.

GROUPBY( DATE_CONVERT( SLICE( A:A ) , "d-MMM-yy" , "yyyy-MM" ) , DATE_CONVERT( SLICE( A:A ) , "d-MMM-yy" , "yyyy-MM" ) , "slice(values,0,1")

Evaluates to: 2012-06, 2012-07, 2012-08, 2012-09, 2012-10, 2012-11, 2012-12, 2013-01, 2013-02, 2013-03, 2013-04, 2013-05, 2013-06.

Step 3: Configuring the Gauge component

With the Current Value sub-component selected, from the formula editing toolbar, remove any existing values by selecting the trash can located far right.

SUM( SELECT( SLICE( B:B , start , end ) , AND( ( DATE_UNITVALUE( DATE( SLICE( A:A ) , “d-MMM-yy” ) , month , first ) = DATE_UNITVALUE( DATE( month , “yyMM” ) , month ) ... ) , ( DATE_UNITVALUE( DATE( SLICE( A:A ) , “d-MMM-yy” ) , year ) = DATE_UNITVALUE( DATE( month , “yyMM” ) , year ) ) , condition ) )

Evaluates to: 100,000

With the Target Value sub-component selected, from the Data tab, formula editing toolbar, remove any existing values by selecting the trash can.

SUM( SELECT( SLICE( C:C ) , ( RIGHT( SLICE( A:A ), 2 ) = 13 ) ) )

Evaluates to: 1,700,000

Related Links: