Calculating the average using two columns in a drilldown table

This Klip shows you how to calculate the average using two columns in a drilldown table. For more information about using drilldown, see Using drill down in a Table component.

This Klip calculates the cost of sales by Country, Province, and Region.

This Klip uses the LOOKUP function and also uses the REF button to reference a formula in another column.

To build this Klip, you will:

  • Build a Table component
  • Create a LOOKUP statement
  • Configure a Table for drilldown

This Klip uses the following data source:

Step 1: Building columns in the Table component

Goal: Use the LOOKUP function to sum sales for Country, Province, and Region.

  1. Select Build a New Klip.
  2. Select the Table 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 Column: Unnamed 1.  
From the formula editing toolbar select Column A - Country from the data source file. A:A
With A:A selected

select Wrap current value in function, Data Manipulation, SLICE.
The SLICE function removes the row header that is in the data source.

SLICE( A:A , start , end )
Repeat the above steps to create column B - Province. Tip: Use Ctrl+C and Ctrl+V to copy and paste the SLICE formula for Country to the other columns. Remember to change the column references.SLICE( B:B , start , end )
Repeat the above steps to create column C - Region SLICE( C:C , start , end )
Repeat the above steps to create column D - Spend SLICE( D:D , start , end )
Repeat the above steps to create columns E - Sales SLICE( E:E , start , end )

select the + symbol to Add three more columns. These columns will be our Average columns for Country, Province, and Region.


With the first new column selected In the formula editing toolbar, select Insert expression in parentheses.
This creates a container for our formula.
( ... )
With ... selected

select the Insert reference to another component button and select Column: Spend.
The REF button allows us to reference a formula in another column.

( Spend ... )
With the first ... selected

select the mathematical operator /.

( Spend / ... )
With the first  ... selected

Select Insert function, Data Manipulation, LOOKUP.
The LOOKUP function helps to keep the data aligned.

( Spend / LOOKUP( input , keys , results ) )
With input selected. select Insert reference to another component and select Column: Country.
( Spend / LOOKUP( Country , keys , results ) )
With keys selected Select Insert function, Data Manipulation, GROUP.
The GROUP function returns unique instances of the values for Country, Province and Region.
( Spend / LOOKUP( Country , GROUP ( values ) , results ))
With values selected Select Insert function, Data Manipulation, SLICE.
The SLICE function removes the row header that appears in the data source.
( Spend / LOOKUP( Country , GROUP( SLICE ( values , start , end ) ), results ) )
With values selected select Column A - Country from the data source file. ( Spend / LOOKUP( Country , GROUP( SLICE ( A:A , start , end ) ), results ))
With Group selected Using Ctrl+C and Ctrl+V, copy and paste to results. Change GROUP to GROUPBY.
THE GROUPBY function will sum all values for sales.
( Spend / LOOKUP( Country , GROUP( SLICE( A:A ) ), GROUPBY( SLICE( A:A ), measure, method )))
With measure selected select Column E - Sales from the data source file. ( Spend / LOOKUP( Country , GROUP( SLICE( A:A ) ), GROUPBY( SLICE( A:A ) , E:E , method )))
With E:E selected

select Wrap current value in function, Data Manipulation, SLICE.
The SLICE function removes the row header that appears in the data source.

( Spend / LOOKUP( Country , GROUP( SLICE( A:A )), GROUPBY( SLICE( A:A ) , SLICE( E:E )))
Repeat to create the Average column for Province. Tip: Use Ctrl+C and Ctrl+V to copy and paste the average formula created for Country to Province and Region columns. Remember to change the column references. ( Spend / LOOKUP( Province , GROUP( SLICE( B:B )), GROUPBY( SLICE( B:B ) , SLICE( E:E )))
For example, here is the Region formula. ( Spend / LOOKUP( Region , GROUP( SLICE( C:C )), GROUPBY( SLICE( C:C ) , SLICE( E:E )))

Step 2: Enabling drill down for the Table component

Goal: To apply grouping to Country, Province, and Region and allow aggregation of other columns.

Before enabling drilldown ensure that the format for Country, Province, and Region columns is set to Text to allow grouping of these columns. All other columns must be formatted as Number and Decimal Places set to (3).

  1. Select the Table component.
  2. Select the Drilldown tab.
  3. Select Enable Drilldown and complete the following tasks.

Field

Required information

 1 For Group by select Country.
 1 For Configure other columns at this level select SUM for Country Average, Spend and Sales. Select hide for Province, Region, Province Average and Region Average.
 1 Select the Add Drill Level button.
 2 For Group by select Province.
 2 For Configure other columns at this level select SUM for Province Average, Spend and Sales. Select hide for Region, Country Average and Region Average.
 2 Select the Add Drill Level button.
 3 For Group by select Region.
 3 For Configure other columns at this level select SUM for Region Average, Spend and Sales. Select hide for Country Average and Province Average.
  Save the Klip.









Feedback and Knowledge Base