For our first article on Tips and Techniques, let’s take a look at the SLICE function.
The primary purpose of the SLICE function is to remove the row or column header. As a result, this is usually the first function you will select when building a formula in the Klip Editor.
You typically use the Wrap current value in function button to wrap the SLICE function around a column or row reference in a formula.
Your data can be manipulated in many different ways using the SLICE function.
- You can select the first value in a column or row.
SLICE (values , 0, 1)
- You can select the last value in a column or row.
SLICE( values , -1 , end )
- You can select a sub-selection of values in a column or row.
SLICE( values, 2 , 4 )
The 1st row counts as row 0. To type in start and end parameters use the Insert Literal String or Number button.
Tip: When troubleshooting a formula, check that the SLICE function is wrapped around all column and row references. If not, when the formula is evaluated, the number of items returned might be different, and the result incorrect.
Did you know there is also a function called FIRST and LAST?
Let’s now look at an interesting example from our Support Desk. In this Value Pair component example, the SLICE function removes the column header of City, Time and Attendance, and returns the last entry for the time period of 3.00 pm for Ottawa.
Tip:You can also add a Prefix using the Properties panel of the Primary Value, or you can use the Insert literal string or number button to add text to the Secondary Value.
The formula is:
GROUPBY( SELECT( SLICE( B:B , start , end ) , (SLICE( A:A, start, end ) = “Ottawa” ) ) ,
SELECT( SLICE( C:C , start, end ), (SLICE( A:A , start, end ) = “Ottawa” ) ) ,
Formula building notes:
To add = Ottawa select
SLICE (A:A), Wrap current value in Parenthesis button, ellipsis (
...), equals to mathematical operator, Insert literal string or number button and type: Ottawa.
For method, use the Insert literal string or number button to type: slice(values,-1). There is no need to type the quotes as they are inserted automatically.
This formula uses the following data source file:
City Time Attendance
Ottawa 3.00 pm 20
Ottawa 3.00 pm 25
Ottawa 3.00 pm 50
Now that you know more about the SLICE function, check out the following tutorials:
- Tips and Techniques - Function examples
- Tips and Techniques - Task examples
- Tips and Techniques - Google Analytics examples
These articles provide an overview of important concepts for building Klips: