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.
  7. 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.
  4. From the formula editing toolbar, select Insert function, Data Manipulation, GROUP.
  5. With values selected, select Insert function, Date and Time, DATE_CONVERT.
  6. With values selected, from the data source, select the column containing the date.
  7. If the date column has a row header, select Wrap current value in function, DATA MANIPULATION, SLICE.
  8. With format in selected, select Insert literal string or number, and type the format of the date as it appears in the data source, eg: (d-MMM-yy), and click the Apply button.
  9. With format out selected, select Insert literal string or number, type the following date format: yyMM, and click the Apply button.
  10. 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.

  11. From the Drop-Down List component, select the Labels sub-component.
  12. From the formula editing toolbar, select Insert function, Data Manipulation, GROUPBY.
  13. From the Drop-down List, values, sub-component, copy the DATE_CONVERT part of the formula using CTRL+C.
  14. Using CTRL+V, paste the DATE_CONVERT part of the formula into the values part of GROUPBY formula.
  15. With measure selected, using CTRL+V, paste the DATE_CONVERT part of the formula.
  16. With method selected, select Insert literal string or number, type slice(values,0,1), and click the Apply button.  This returns a unique instance of the date.
  17. 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

  1. With the Current Value sub-component selected, from the formula editing toolbar, remove any existing values by selecting the trash can located far right.
  2. Select Insert function, Data Manipulation, SELECT.
  3. With data selected, select the column that contains the current values.
  4. If the current values column has a row header, select Wrap current value in function, DATA MANIPULATION, SLICE .
  5. With condition selected, select Insert function, Date and Time, DATE_UNITVALUE.
  6. With values selected, select the column containing the date.
  7. If the date column has a row header, select Wrap current value in function, DATA MANIPULATION, SLICE.
  8. With SLICE and the date column selected, select Wrap current value in function, Date and Time, and DATE.This turns the date into an epoch number.
  9. With format selected, click Insert literal string or number, and type the format of the date that appears in the data source, eg d-MMM-yy, and click Apply.
  10. With unit selected, select month.
  11. With the first ellipses selected, select the equals operator.
  12. With the first ellipses selected, select Insert function, Date and Time, DATE_UNITVALUE.
  13. With values selected, select Insert Variables and then month.
  14. With month selected, select Wrap current value in function, Date and Time, DATE.
  15. With format selected, click Insert literal string or number, and type in the date format yyMM, and click Apply.
  16. With unit selected, select month.
  17. With the ( and the first DATE_UNITVALUE part of the formula selected, select Wrap current selection in function, Logic, AND.
  18. Using CTRL+C copy the ( and the first DATE_UNITVALUE part of the formula to condition using CTRL+V.
  19. Change the month part of the DATE_UNITVALUE part of the formula to year.
  20. With the word SELECT selected, highlighting the whole formula, click Wrap current value in function, SUM.
  21. 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

  22. With the Target Value sub-component selected, from the Data tab, formula editing toolbar, remove any existing values by selecting the trash can.
  23. Select Insert function, Data Manipulation, SELECT.
  24. With data selected, select the column with the predicted value from the data source.
  25. If the predicted value column has a row header, select Wrap current value in function, DATA MANIPULATION, SLICE.
  26. With condition selected, select the column with the date.
  27. If the date column has a row header, select Wrap current value in function, DATA MANIPULATION, SLICE.
  28. With SLICE of the date column selected, select Wrap current value in function, Text, RIGHT.
  29. With number selected, select Insert literal string or number, type in 2, and click the Apply button.
  30. At the end of the formula, select the first ellipses, and click the = operator.
  31. With the first ellipses selected, select Insert literal string or number type 13, and click the Apply button. This returns all values for the year 2013.
  32. With SELECT selected, select Wrap current value in function and select SUM.
  33. SUM( SELECT( SLICE( C:C ) , ( RIGHT( SLICE( A:A ), 2 ) = 13 ) ) )

    Evaluates to: 1,700,000

  34. Click the Save button.
  35. Type a name for your Klip.
  36. Click the Finished button.

Step 4: Troubleshooting Tips

  1. Verify that the Date Format is how the date appears in the data source.
  2. Test the variable by specifying a Klip Editor Value for the month, eg 1206.
  3. Test each part of the formula to verify that it evaluates correctly by using the Evaluate Current Selection button.

Related Links:

    For more information on how to set up indicators, see How to use Indicators

Feedback and Knowledge Base