Placeholder

Customer Forum

Reporting Tips & Tricks: How to "slice & dice" data

Workbooks Support Posted: 2011-05-26 10:28

It is a relatively common requirement to want to manipulate data into a slightly different format than that in which it's stored e.g. Create a report based on the first 2 characters of a postcode to perform geographical analysis.

This forum article explains how to manipulate data using the Calculated Column's Formula builder functionality:

 

LEFT & RIGHT Operators

Allows you to select a specified number of characters from the left or right of a field, such as the first 2 characters of a Postcode.  

Syntax: LEFT or RIGHT (field_name, [number of characters] )

Example: LEFT(main_postcode_or_zipcode, 2)

This example shows two calculated columns.  LEFT has been used to select only the first two characters of the postcode.  COUNT has been used to tally the records.


NOTE: The MID operator is very similar, but rather than picking from the right or left of a field you specify where to start and the number of characters e.g. MID(name, 3, 2).  This will return the 4th and 5th characters of a field.

 

CONCAT Operator

Allows you to join/concatenate two or more fields, such as Company Name and Town/City into the same field to output data in a single column e.g. Speedy Cabs (Reading).

NOTE: Although joining 2 or more fields is simple you are likely to want to separate text with a spacing character, hyphen, or enclose in brackets.  To achieve this simple enclose the character in single quotes e.g. ' ' will output a space, '(' will output a bracket.

Syntax: CONCAT (field_name_1, ' ', field_name_1)

Example 1: CONCAT (name, ' ', main_town_or_city) 

Example 2: CONCAT (name, ' (', main_town_or_city, ')'  ) 

 
External Posted: Sat, 19.10.2013 - 09:43

In the next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps;

  • Create a new blank report based on People. Within the Details view add a column for Object Reference, Person Name, Postcode & Town or City.
  • Add a Calculated column called 'Left Example' and use the following formula: 

'LEFT( main_postcode_or_zipcode, 2)'

This will display the first 2 letters of a Persons Postcode.

The details view now shows all People, along with their Postcodes and a column to show the first 2 letters of a Person Postcode. We can now add a summary view to carry out the remaining steps. To do this:

  • Select 'Add Summary View' and 'Add grouping column'. Group by 'Left Example'.
  • Add a Calculated column called 'Tally', which uses the following formula:

'COUNT(PARENT('Object Reference'))'

This formula will create a tally of the number of People whose Postcode begins with each 2 letter combination.

To show the Concatenation example:

  • Add a new Summary View.
  • Add a Calculated column called 'Concat', which uses the following formula;

'CONCAT( PARENT('Person name'), ' ',  PARENT('Town or City'))'

This formula concatenates the Person name and their Town into a single column.