Common User Input Control scenarios: Counting all tickets by quarter, by month, by weekday

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 counts the date for all tickets and displays the results as a quarterly, monthly, or weekday date range.

The SWITCH function uses a variable that allows the user to select a date range of either quarter, month or weekday from a drop-down list. For more information on the SWITCH function, see SWITCH. For more information about using variables, see User Input Control component.

This Klip uses the DATE_UNITVALUE function which is responsible for retrieving dates for the time periods: quarter, month, or days of the week.

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 using the SWITCH function in the X and Series sub-components.

This Klip uses the following data source:

Step 1: Building a User Input Control component

  1. Select Build a New Klip.
  2. Select the User Input Control component.
  3. Select Don't use a data source option.

Goal: Create a variable to use in Bar/Line chart formulas.

  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. Period selection
Create New Variable Enter the name for the variable to be used in the Bar/Line chart formulas. kperiod
  1. From the component tree, select Values.
  2. From the formula editing toolbar, select Insert function, ARRAY.
  3. With data selected, click the Insert literal string or number button and type quarter.
  4. Repeat Step 9 for month and weekday selections.
  5. Save this Klip.

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

Goal: Use the GROUP function to return a unique instance of values for a given time period.

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. This data source has a row header. If your data source does not, you can skip this step in the instructions.

  1. Select Build a New Klip.
  2. Select the Bar/Line Chart component.
  3. Select 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, Logic, SWITCH. SWITCH( data , case , value )
With data selected select the Insert variable button and select the reference you created earlier. This allows the user to switch the view to either quarter, month or days of week. SWITCH( kperiod , case , value )
With case selected select the Insert literal string or number button, type in quarter and select the Apply button. Note: It must be the same case as you typed in the User Input Control component. SWITCH( kperiod , "quarter", value )
With value selected select Insert function, Text, CONCAT. To retrieve quarter values, the formula joins both year and quarter values. SWITCH( kperiod , "quarter", CONCAT( data ) , case , value )
With data selected select Insert function, Date and Time, DATE_UNITVALUE. The DATE_UNITVALUE function allows you to retrieve dates for a quarter, month, weekday. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( values , unit , first ) , data ) )
With values selected select Insert function, Date and Time, DATE. The DATE function turns a human readable date into epoch time. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( dates , format , timezone ) unit , first )))
With dates selected select the date column - Column B. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( B:B , format , timezone ) unit , first )))
With B:B selected.  If your data source does not have a row header, skip this step. select Wrap current value in function, Data Manipulation, SLICE. This removes the row header from the column. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B , start , end ) , format , timezone ) , unit ) )
With unit selected from the Insert an option box, select year. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B )) , year , first ) , data ) )
With DATE( selected in the existing formula select format and select the Insert literal string and number button, type in the date format from the data source, and click the Apply button. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" , timezone ) , year , first )))
With DATE_UNITVALUE selected using Ctrl+C and Ctrl+V, copy and paste the formula into data and change year to quarter. You have created the date formula for quarter. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ) , year ) , DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), quarter , first ) , data ) )
With kperiod selected select case, click the Insert literal string or number button, type month, and select the Apply button. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B )"M/dd/yy" ), year ), DATE_UNITVALUE( DATE( SLICE( B:B ), "M/d/yy" ), quarter ) ) , "month" , value )
With CONCAT selected using Ctrl+C and Ctrl+V, copy and paste the formula into value and change quarter to month . You have created the date formula for month. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), month , first ), data ) )
With kperiod selected select case, click the Insert literal string or number button, type weekday, and click the Apply button. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) ,"M/d/yy" ) , year ) DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ) , quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , M/d/yy" ) , year ) DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), month ) ) , "weekday" , value)
With CONCAT selected using Ctrl+C and Ctrl+V, copy and paste the formula into value and change month to day of week. You have created the date formula for day of week. SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/dd/yy" ), quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ) , month ) ) , "weekday" ) , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ) , day of week, first ), data ) )
With SWITCH selected. select Wrap current value in function, Data Manipulation, GROUP. This returns a unique instance of the date range. GROUP(SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), month) ) , "weekday" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), day of week ) ) , case , value ))
With kperiod selected for the Klip Editor Value, type quarter. Doing this, allows you to test the formula.  
With GROUP selected select Evaluate current session to verify the formula is working correctly. Result: 20133
With the Properties panel selected provide a name for the Axis title, select Show axis title and set Sort Order to Ascending.  

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

Goal: Use the GROUPBY function to count the 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 formula editing toolbar of the Series: Untitled sub-component and change GROUP to GROUPBY.

GROUPBY (SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ) , "month" ) , "weekday" ) , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), day of week ))) , measure , method )
With measure selected select the date column - Column B.  GROUPBY (SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ) , "month" ) , "weekday" ) , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), day of week ))) , B:B , method )
With B:B selected. If your data source does not have a row header, skip this step. select Wrap current value in function, Data Manipulation, SLICE. The SLICE function by default removes the row header from the column. GROUPBY (SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ), DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ) , "month" ) , "weekday" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) , "M/d/yy" ), day of week ))) , SLICE( B:B , start , end ) , method )
With method selected select Insert literal string or number, type
count(values) and click the Apply button. By default GROUPBY sums the values, in this case the dates need to be counted.
GROUPBY (SWITCH( kperiod , "quarter", CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" , year   ) , DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), quarter ) ) , "month" , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), "month" )) , "weekday" ) , CONCAT( DATE_UNITVALUE( DATE( SLICE( B:B )"M/d/yy" ), year ) , DATE_UNITVALUE( DATE( SLICE( B:B ) "M/d/yy" ), day of week ))) , SLICE( B:B ) , "count(values)" )
With GROUPBY selected. select Evaluate Current Session to verify the formula is working correctly. Result: 10 items
Select the Properties panel. name the Series Label.  
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