Adding Table Calculations

Customer Insights' default Explores all include the Count measure, and two Explores also include the Count Distinct measure. Both of those measures are extremely useful in analyzing data and in constructing visualizations. However, there will likely be times when you want to do something besides counting up the number of Xs in a dataset. That’s where table calculations come in.

Table calculations (created by using a formula syntax similar to that used in Microsoft Excel) provide a way for you to add custom measures to a data table (and, by extension, to a visualization). For example, suppose you have a table similar to this one, a table that shows legacy sign-ins on a month-by-month basis:

Let’s further suppose that you had set a goal of 35,000 sign-ins per month (420,000 sign-ins for the year). From the table, it’s easy to see that you met the overall goal: you had 459,070 sign-ins during the year. But how often did you meet the monthly goal, and by how much did you meet (or fail to meet) that goal. That’s not quite as easy as to say, at least not without doing a little math.

Doing a little math is good; adding a table calculation is better. When you add a table calculation, you add a new column to the table; that column contains whatever data is derived from the table calculation. To see what that means in real life, complete the following procedure:

  1. In the Data section of your Look, click Calculations:
  2. In the Table Calculations dialog box, replace the title Calculation 1 with the title Percent Difference from Goal:
  3. Click Default Formatting and then click Percent (0) 123,457%:

    In case you’re wondering, we want to show the percent difference between the target value 30,000 and the actual number of sign-ins. We want the value specific as a percentage (and without decimal points), which is why we selected Percent (0) 123,457%.

  4. In the formula section (directly below the title) type $. You should see something similar to this:

    This is the list of dimensions and measures that are used in your query. To determine the percent difference, we need to take the value of the Event Fact Count field and divide it by 35,000. In the dialog box, select Count ${event_fact.count}. The formula field will look like this:

  5. Now complete the formula by adding** / 35000** and then click Save Table Calculations:
  6. Your data table will immediately redraw itself to look like this:

Now it’s much easier to see whether we succeeded or failed to reach our goal, and by how much (good or bad) we deviated from that goal.

Tip: Want to make it even easier to see whether we succeeded or failed to reach our goal each month? In that case, add some conditional formatting to the table. Without detailing all the steps, in this visualization we’re asking Customer Insights to colorize any monthly totals that failed to the goal. In turn, we’ll get a visualization similar to the following:

Here’s another cool way to use table calculations. As you might recall, our original table shows sign-ins on a month-by-month basis:

But what if you wanted to know more about sign-in trends, what if you wanted to know how registrations for a given month compared with registrations for the previous month? That can be a very interesting question, and Customer Insights includes a nifty feature - offset - that helps you answer that question.

By default, table calculations compare values in the same row. In this case, however, we want to compare values in different rows; for example, we want to compare the total sign-ins in the June row with the total sign-ins in the May row. With offset, we can compare a row with the previous row (by setting the offset to 1) or with the next row (by setting the offset to -1). Here’s how we do that:

  1. In the Data section of your Look, click Table Calculations.
  2. In the Title field, type Month-to-Month Trends.
  3. Click the formatting list and then click Decimals (0) - 1,235.
  4. In the Table Calculations dialog box, type $.
  5. Select Count ${event_fact.count}.
  6. Type - o. and then click offset(Any,Number). Your formula will look like this:
  7. With the cursor inside the parentheses, type $ and then click Count ${event_fact.count}:
  8. Type ,1 and then click Save Table Calculations:

Here’s your new visualization:

Note: If you click Calculations, you’ll bring up the Table Calculations dialog box. From there, you can apply formatting, change the column title, etc.