Filtering by market segment to display total sales by month

Sometimes, a data source presents the data in rows, instead of columns.  This is the case in this data source. These instructions show you how to integrate a user input control component with a chart component.

  1. Use a drop down menu to filter data by market segment, and
  2. Display total sales by month using the y and x axis respectively.
Example: Filtering by market segment to display total sales by month

This example uses the following data set.

A

B

C

D

Month Market Segment 1 Market Segment 2 Market Segment 3
January 1000 3000 5000
February 2000 4000 6000
March 1000 3000 5000
April 2000 4000 6000
May 1000 3000 5000
June 2000 4000 6000

Step 1: Building and configuring a user input control component

  1. Select Build a New Klip.
  2. Select the User Input Control component.
  3. Select Use an existing data source from the library option and select your data source.
  4. From the component tree, select Klip, and type a Klip Title. For example, Sales by market segment.
  5. From the component tree, select the Drop-Down List.
  6. At the Properties panel, provide the required information:

Field

Required information

For our example

Control Label Enter a name for the drop down list. Select a market segment
Create New Variable Enter a name for the cells that will filter data. Msegment
Default Value Enter a name for the default selection value. MS1

Goal: Create an ARRAY statement.

Location

Action

For our example

From the component tree Select Values  
From the Data panel, formula editing toolbar Select the cells to filter data B1:D1
In the formula editing toolbar, with B1:D1 selected Click Wrap current value in function, ARRAY ARRAY( B1:D1 ) , data)
In the formula editing toolbar Click ARRAY, Evaluate Current Selection to verify that the result is MS1, MS2, MS3, and then click Finished.  
  Click Save  
  Name the Klip  
  Click Finished  

Step 2: Building and configuring a chart component

  1. Select Build a New Klip.
  2. Select the Bar/Line Chart component.
  3. Select use an existing data source from the library option and select your data source.
  4. Select Klip from the component tree.
  5. At Klip Title, enter a name for the Klip. For example, Chart for {Msegment}
  6. Note: Where the {} brackets changes to the segment number selected from the drop-down menu.

    Goal: Name sub-components.

  7. Select the Y Axis from the component tree and perform the following actions.
    1. Enter a name for the Axis Title. For example, Total sales.
    2. Select Show axis line.
  8. Select the Series sub-component from the component tree.
  9. Select the Properties panel, locate the Series Label, and enter a name. For example, Market Segment.
  10. Select the X Axis from the component tree.
  11. Select the Properties panel and perform the following actions:
    1. Enter a name for the Axis Title. For example, Month.
    2. Select Show axis line.
    3. Select a Label Angle.

    Goal: Configure the X Axis to display the month.

    Location

    Action

    For our example

    Select the X Axis, Data panel, formula editing toolbar. Select the column that contains the month. A:A
    If the column has a row header, with A:A selected. Click Wrap current value in function, Data Manipulation, SLICE. SLICE( A:A, start, end )
    In the formula editing toolbar. Click SLICE, Evaluate Current Selection to verify that January, February, March, April, May, June are returned. SLICE ( A:A , start , end ) , data )

    Goal: Create a SWITCH statement for the Series sub-component.

    Location

    Action

    For our example

    Select Series, Data panel, formula editing toolbar. Select Insert function, Logic, SWITCH. SWITCH( data , case , value )
    With data selected. Select Insert Variable and select the Msegment variable. SWITCH( Msegment , case , value )
    With case selected. Select Insert literal string or number, enter the name of the first segment, and click the Apply button. SWITCH( Msegment , "MS1" , value )
    With value selected. Select the first segment column from the data source. SWITCH( Msegment , "MS1" , B:B  , case , value )
    If the column has a row header, in the formula editing toolbar, with MS1 selected. Select Wrap current value in function, Data Manipulation, SLICESWITCH( Msegment , "MS1" , SLICE( B:B ) , case , value )
    With case selected. Select Insert literal string or number, enter the name for the second segment, and click the Apply button . SWITCH( Msegment , "MS1" , SLICE( B:B ) , "MS2" , value )
    With value selected. Select the second segment column from the data source. SWITCH( Msegment , "MS1" , SLICE( B:B ) , "MS2" , C:C )
    If the column has a row header, in the formula editing toolbar with MS2 selected. Select Wrap current value in function, Data Manipulation, SLICE . SWITCH( Msegment , "MS1" , SLICE( B:B ) , "MS2" , SLICE( C:C ) , start , end )case , value )
    Repeat the above two steps for Market Segment 3. Until you have the following formula. SWITCH( Msegment , "MS1" , SLICE( B:B ) , "MS2" , SLICE( C:C ) , "MS3", SLICE( D:D start , end ) , case , value )
    In the formula editing toolbar. Select SWITCH, Evaluate Current Selection to verify that the values of 1000, 2000, 1000, 2000, 1000, 2000 are returned. SWITCH( Msegment , "MS1" , SLICE( B:B ) , "MS2" , SLICE( C:C) , "MS3", SLICE( D:D ) , case , value ) .
  12. Click Save.
  13. Name the Klip.
  14. Click Finished.

Step 3: Troubleshooting Tips

  1. If the selection from the drop-down menu list does not display data in the chart, edit the user input control component, in the ARRAY formula, delete the selected values (B1:D1) and type in the values (M1, M2, M3).

Feedback and Knowledge Base