Common User Input Control scenarios: Displaying total boxes of fruit sold in the last three months

Some formulas are used more often than others when building Klips. In this series of articles, a Klip is built using a common set of formulas to give a better understanding of how these formulas work within a sample data set.

This Klip displays the total number of boxes sold for three different fruit types in the past three months.

This Klip uses a variable that allows the user to select from a drop-down list. For more information about using variables, see User Input Control component.

This Klip uses the SELECT function in order that AND and OR conditions can be specified. The OR condition allows the user to select from either all fruit, or Bananas, or Apples, or Oranges. The AND condition restricts the results to the last three months. The time period is specified using the BETWEEN function and is easily adjustable by changing the unit and amount values within the DATE_ADD function.

To build this Klip, you will:

  • Build a User Input Control component
  • Create a variable
  • Build a Bar/Line Chart
  • Create GROUP and GROUPBY statements that use the SELECT function for the X and Series sub-components.

This Klip uses the following data source:

Step 1: Building a User Input Control component

Note: If the column has a row header in your data source, wrap each column reference in the SLICE function. By default, the SLICE function removes the column or row header. For more information, see SLICE function.

  1. Select Build a New Klip.
  2. Select the User Input Control component.
  3. Select Use an existing data source from the library and select your data source.

Goal: Create a variable for formulas in the Bar/Line chart.

  1. From the component tree, select Klip, and type a Klip Title.
  2. From the component tree, select Drop-Down List.
  3. At the Properties panel, provide the required information:

Field

Required information

For our example

Control Label Enter a name for the drop down list. Fruit selector
Create New Variable Enter a name for the variable. fruit
  1. From the component tree, select Values.
  2. In the formula editing toolbar, select Insert function, ARRAY.
  3. With data selected, click the Insert literal string or number button, type All and click the Apply button. This allows the user to select all fruit types.
  4. With data selected, select the column that contains the fruit type from the data source - column B. This allows the user to select either bananas, apples, or oranges.
  5. With ARRAY selected, select Wrap current value in function, Data Manipulation, GROUP, to return one name for each type of fruit.
  6. Save this Klip.

Step 2: Building the X-Axis of a Bar/Line Chart component

Goal: Use the GROUP function to return a unique date.

Note: If the column has a row header in your data source, wrap each column reference in the SLICE function. By default, the SLICE function removes the column or row header. For more information, see SLICE function.

  1. Select Build a New Klip.
  2. Select the Bar/Line Chart component.
  3. Select either Use an existing data source from the library and select your data source.
  4. From the component tree, select Klip, and type a Klip Title.

Location

Action

For our example

From the component tree select X:Axis: Untitled.  
In the formula editing toolbar select Insert function, Data Manipulation, GROUP. The GROUP function returns one instance of the date. GROUP( values )
With values selected select Insert function, Data Manipulation, SELECT. The SELECT function lets you create AND OR condition statements. GROUP( SELECT( data , ( condition ) ) )
With data selected select the column containing the date - Column A. GROUP( SELECT( A:A , ( condition )))
With condition selected select Insert variable and select the variable you created earlier.  The variable allows for the selection of a fruit type or all fruit from a drop down list. GROUP( SELECT( A:A , ( fruit ...)))
With the first  ... selected select the Equals to operator. GROUP( SELECT( A:A , ( fruit = ...)))
With the first ... selected select Insert literal string or number, type All, and click the Apply button. GROUP( SELECT( A:A , ( fruit = "All" ... )))
With ( fruit = "All" ...  )selected select Wrap current value in function, Logic, OR. GROUP( SELECT( A:A , OR (( fruit = "All"... ) , condition )))
With condition selected select Insert variable and select the variable you created earlier. GROUP( SELECT( A:A , OR(( fruit = "All" ) , fruit , condition )))
With fruit selected Select Wrap current value in parenthesis. GROUP( SELECT( A:A , OR(( fruit  = "All" ) , ( fruit ... ) , condition )))
With the first ... selected Select the Equals to operator. GROUP( SELECT( A:A , OR((fruit = "All" ) , ( fruit =... ) , condition )))
With the first ... selected Select the column containing the fruit type - Column B. You have created an OR condition that allows the user to select either ALL, Bananas, Apples, or Oranges. GROUP( SELECT( A:A , OR( ( fruit = All" ) , ( fruit = B:B ... ) , condition )))
With OR selected in the existing formula Select Wrap current value in function, Logic, AND. The AND condition allows the time period to be restricted to the last three months. GROUP( SELECT( A:A , AND( OR( (fruit = "All" ) , ( fruit = B:B ) , condition ) , condition )
With the second condition selected Select Insert function, Logic, BETWEEN. GROUP( SELECT( A:A , AND( OR( ( fruit = "All" ) , ( fruit = B:B ) , BETWEEN( values, start , end ) , condition )
With values selected select the column containing the date - Column A. GROUP( SELECT( A:A , AND( OR( ( fruit = "All" ) , ( fruit = B:B ) , BETWEEN( A:A, start , end ) , condition ) ) )
With A:A selected select Wrap current value in function, Date and Time, DATE. The DATE function converts the date to a number -  an epoch number. GROUP( SELECT( A:A , AND( OR( ( fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , format , timezone ) , start , end ) ) )
With format selected select Insert literal string or number, type the format in your data source and click the Apply button. GROUP( SELECT( A:A , AND( OR( ( fruit = "All" ) , ( fruit = B:B ), BETWEEN( DATE( A:A , "MM/dd/yy" , timezone ) , start , end ) )
With start selected select Insert function, Date and Time, DATE_ADD. The DATE_ADD function allows you to restrict the dates to the last three months.GROUP( SELECT( A:A , AND( OR( (fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( values , unit , amount ) , end ) ) )
With values selected select Insert function, Date and Time, TODAY.
The TODAY functions allows the time period to start at Today's date.
GROUP( SELECT( A:A , AND( OR( ( fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY ( timezone ) , unit , amount ) , end ) ) )
With unit selected From the Insert An Option menu, select month.GROUP( SELECT( A:A ,  AND( OR( ( fruit  = "All" ) , ( fruit =  B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ) , month , amount ) , end ) ) )
With amount selected select Insert literal string or number, type in -3 and click the Apply button.
You must type in -3 to return the last three months.
GROUP( SELECT( A:A ,  AND( OR( (fruit =  "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ), month , -3) , end ) ) )
With end selected select Insert function, Date and Time, TODAY.

GROUP( SELECT( A:A ,  AND( OR( ( fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ) , month , -3 ) ,TODAY(  timezone ))))
With the first column reference to A:A selected in the formula select Wrap current value in function, Date and Time, DATE CONVERT. The DATE_CONVERT function lets you convert the format of the date in the data source to another date format. GROUP( SELECT( DATE_CONVERT( A:A , format in , format out ),  AND( OR( (fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ) , month , -3 ) ,TODAY( ) ))))
With format in selected select Insert literal string or number, type in the format from your data source and click the Apply button.GROUP( SELECT( DATE_CONVERT( A:A , "MM/dd/yy" , format out ),  AND( OR( (fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) ,DATE_ADD( TODAY( ) , month , -3 ) ,TODAY( ) ))))
With format out selected select Insert literal string or number, type in the format you want to display in the Klip and click the Apply button. Note: To have the sort correctly start the format with yyyy. GROUP( SELECT( DATE_CONVERT( A:A , "MM/dd/yy" , "yyyy/MM/dd" ) , ( AND OR( (fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ) , month , -3 ) ,TODAY( ) ) )
With GROUP selected select Evaluate current session to verify the formula is working correctly. Result: Verify the number of items returned matches the last three months (9).
With the Properties panel selected provide a name for the Axis title, set the Format as to Date and Time with an Output Format of Custom yyyy/MM/dd, set the Label Angle to vertical, and check Show the axis title.  

Step 2: Building the Series of the Bar/Line Chart component

Goal: Use the GROUPBY function to count date entries for a given time period.

Location

Action

For our example

With the formula of the XAxis selected using Ctrl+C and Ctrl+V, copy and paste the formula into the Series: Untitled sub-component formula editing toolbar and change GROUP to GROUPBY.
The GROUPBY function allows us to sum all values for a given day.

GROUPBY( SELECT( DATE_CONVERT( A:A , "MM/dd/yy" , "yyyy/MM/dd" ) , AND( OR( (fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ) , month , -3 ) ,TODAY( ) ) ) ) ) ) , measure , method )
With SELECT selected within the existing formula using Ctrl+C and Ctrl+V, copy and paste the formula into measure and then select DATE_CONVERT( A:A , "MM/dd/yy" , "yyyy/MM/dd" ) and select the column with the values you want to sum - column C:C. GROUPBY( SELECT( DATE_CONVERT( A:A , "MM/dd/yy" , "yyyy/MM/dd" ) , AND( OR( (fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ) , month , -3 ) , TODAY( ) ) ) ) ) ) , SELECT( C:C  , AND( OR( (fruit = "All" ) , ( fruit = B:B ) , BETWEEN( DATE( A:A , "MM/dd/yy" ) , DATE_ADD( TODAY( ) , month , -3 ) ,TODAY( ) ) ) ) ) , method )
Note: Leave method as is.   The default action for method in the GROUBY function is to sum all values.
With GROUPBY selected. select Evaluate Current Session to verify the formula is working correctly. Result: Verify the number of items returned matches the last three months (9).
Select the Properties panel. name the Series Label and  
Select the Y Axis sub-component. name the Axis Title and select Show axis title.  
  save the Klip.  

Klip Tutorials

  1. Introduction
  2. Introduction to the Build-a-Klip Tutorial
  3. Build-a-Klip Tutorial: Phase One
  4. Build-a-Klip Tutorial: Phase Two
  5. Build-a-Klip Tutorial: Phase Three
  6. Build-a-Klip Tutorial: Phase Four
  7. Build-a-Klip Tutorial: Phase Five
  8. Example - Revenue YTD Klip
  9. Example - Financial Performance YTD Klip
  10. Example - World News Klip
  11. Example - Bookings by Country Klip
  12. Example - Bookings QTD Klip
  13. Example - Stock Quotes Klip
  14. Example - Marketing Traffic Sources Klip
  15. Example - 75/20 Service Level - Today Klip
  16. Training Klip - Array, Slice, Reverse, If, and Trim Functions
  17. Training Klip - Sum, Average, and Count Functions
  18. Training Klip - Group and Groupby Functions
  19. Training Klip - Date, Datevalue, and Today Functions
  20. Training Klip - Replace function and image URLs
  21. Training Klip - Concat and Join Functions
  22. Common Bar/Line Chart scenarios: Example of using the LOOKUP function with a condition to align data
  23. Common Bar/Line Chart scenarios: Example of using the LOOKUP function to align data
  24. Common User Input Control scenarios: Displaying total boxes of fruit sold in the last three months
  25. Common User Input Control scenarios: Counting all tickets by quarter, by month, by weekday
  26. Common Table scenarios: Selecting start and end dates to display number of boxes sold by country
  27. Common Table scenarios: Sorting data to display the video with the highest number of views
  28. Common Pie Chart scenarios: Displaying total number of surveys as a percentage by country
  29. Common Pie Chart scenarios: Displaying numbers and percentages in a pie chart legend

Feedback and Knowledge Base