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. Select Build a New Klip.
  2. Select the Gauge component.
  3. Select Use an existing data source from the library.
  4. Select your data source file.
  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: