ArticlesBite Size Analytics: How to Compare Tables

When it comes to Analytics, there is a great deal of value to be had from building lenses. A lens is the Salesforce name given to either a chart or a table of data in analytics. One such lens is a compare table and this byte will give an introduction to that functionality.

Getting Started

1. From Analytics Studio, navigate to a dataset that will be used as the source for your pivot lens. For AC, you may want to use Financial Transactions for any transaction-based reporting and Financial Balances if you want to report on both Transactions and budget details [You can filter by the Balance Type field to report on budgets]. 
2. Open the dataset to start the creation of a lens as below.
   

   

   • Note that although we have chosen a Financial Transactions dataset, any dataset across either PSA, FM, RM, BC, SCM or Custom can be used to create a lens.
3. Use the lens options to switch to either a compare table. This is in the top right.
   
   
   
4. Once you have created a Lens and chosen the option for compare table, your view will look similar to below
   
   
   
5. Choose a column value. By default, this will be Count of Rows.
6. Click on the text to choose from measures in your dataset. In the case of the Financial Transactions dataset, when you type "Dual" you’ll see options for dual value.
   

   

    

   • Note that in many of the Financial Datasets an (s) on the end of a measure will mean it is reversed. This allows you to format the sign of sections. You can add as many columns as you like. 

7. Add Groups to your table. These are text (aka Dimension) and date fields. You can add up to 4 groupings through the UI but more can be added here. Once you’ve added a few groups and columns it will look similar to below.

Compare Table Column Filter

Filters can be applied to the whole lens through the filters option in the top left. To filter an individual column, it is important to note that there is a difference between clicking on a column's text (e.g. Sum of Dual Value) and clicking on the drop-down arrow. Click on the drop-down arrow on the columns listed on the left to see the following options:

There are options for sorting, rearranging, and cloning. To select a value to filter by, choose the Add a Filter option.

Multiple filters can be added to columns. 

Compare Table Column Rename

Sometimes the column titles displayed on a Lens doesn’t quite represent the column, especially if filters have been applied. To rename these, it is important to note that there is a difference between clicking on a columns text (e.g. Sum of Dual Value) and clicking on the drop-down arrow. Click on the drop-down arrow on the columns listed on the left, to see the following options:

There are options for sorting, rearranging, filtering, and cloning. To see the editing menu, select the Edit this Column option. 

The column you are editing will be highlighted on the right and on the left the option we are interested in is Column Header. Column Alias can nearly always be ignored. Enter a value and then press Apply, then Close. Your columns will now be renamed. When editing multiple columns it can also be useful to make use of the left and right arrows in the top left of the Editing Column title to navigate between columns. 

Compare Table Column Calculations

Sometimes when you build reports you may want a calculation between two columns. Maybe a ratio, maybe a total. To do this, add a column like the others for measures but pay attention to the Add Formula button in the top right of the pane that opens. 

This opens a side panel that can be used to create formula.

The important thing to note is the letters above each of the columns, in the case above A, B, C. These are the column aliases and can be used to create formulas. Simple items like A+B will produce a total. You can perform any maths operations with more complex functions available too. Note that the formula builder is case sensitive.

How Analytics Deals with Null Values

In the example below we can see that a formula column has been created to sum together columns A and B but for one entry no value is displayed in the total column.

This is because there was no value for Column A. When there is no value Analytics treats it as a null. Null is essentially text and not 0 as you might assume. So in the case above we can’t add text to numbers and so we get another null in the total column. To combat this we must use a special function that returns the first non-null value. This function is called coalesce. You list each of the values by commas and the first number it finds will be displayed.

coalesce(A,0) - This would return the value A if it is a number, otherwise it would return 0. 

Updating the formula above would make the value work. This function can be used in other scenarios such as when dividing by zero and is one you will use lots.

See more on coalesce here.

Complex Filtering

Filtering is a big part of any report and filtering in analytics is easy. When building any new Lens you have a filter tab next to where you are defining your columns and groups.

You can use this to add filters for any fields you like. Dates, Dimensions and Measures. Using the drop-down arrow at the top you will then be allowed to add filter logic.

This allows you to define complex criteria using the functions AND, OR, NOT in combination with brackets. 

The options of what you can do with compare tables are limitless when you combine column filters and calculations, which is why they are my favourite tool to use from analytics.

If you have any feedback or requests post in the Reporting Chatter group.