Hi. How can we help?

Creating calculations to group and summarize results

You can build in-table summaries of the results in Analytics. 
For example, if you want to summarize results by the subcategory only, or use the top-level and secondary categories together. You can build calculations into Analytics to build sums and gather these together in a customized way that's useful for you. 
For example, if you started with the First Subcategory report that you customized earlier, where you start with the complete category, but then remove everything following the subcategory. 

You could prepare data to gather totals by First Subcategory, but you need Calculations to do this, because you can't use a calculated field as a dimension. 

Step one: Sort by First Subcategory

You can sort by the first category and identify the last instance of every First Subcategory by clicking the Item Category label.

Step two: Determining the last instance of each First Subcategory

  1. Determine which is the last instance of each First Subcategory. Start by using a simple Offset function to look at the First Subcategory field.
  2. Add a comma and 1 (,1) to make the calculation refer to one row down.
  3. Save and run the calculation. In the report to the right of each First Subcategory, you should see the next First Subcategory.

  4. Then, to see if they are the same, use one more simple calculation:

     = If the values are equal, Analytics will return a Yes, if not, Analytics will return a No. 
    So start with two sets of brackets with an equal sign in the middle.

  5. In the first set of brackets, search and enter the First Subcategory calculation.

  6. Copy the offset function looking at the row below, and then paste it into the second set of brackets.

  7. Save and run the calculation. You'll get a Yes/No calculation where the No identifies the final instance of every First Subcategory.

  8. Add a title and description, and click Save As New

Step three: Determining how many rows are the same

Now that you know the last instance of each First Subcategory. Next you'll also need a count of each one. A way to start this involves the Match function, which returns the first row on which a value occurs. What you will do is:

  1. In a new calculation, enter match(). In the brackets, search and enter the first subcategory.

  2. In the brackets, after the first subcategory, add a comma, search and enter the first subcategory.

  3. Save, and on your report you'll get a Calculation repeating the first row on which each First Subcategory occurred.
  4. Next, start a Calculation where you return the row().

  5. Then include a subtraction symbol and another set of brackets.

  6. In the second set of brackets, copy and paste the matched reference.

  7. Save and run the calculation. You'll get a 0 for each First instance of a First Subcategory, and a subsequent count on each row of the subcategory.



     

Step four: Creating a first and last reference point

The Offset List function we're going to use will need three arguments, 
the values (which will be our Sales Line totals) 
our start point for counting (or, how far back we want to start) 
the end point for counting (or, how many rows we wish to include) 
Our values are already on the table. 
Our start point now will be the negative row count for each last instance of a First Subcategory. So this becomes a pretty simple IF statement. 
If we're looking at the last instance of the First Subcategory, show me the negative count, if not,return a 0 

  1. Start with brackets... 
  2. Then copy the calculation looking at the reference being the same or not.

  3. Paste, the calculation, and copy and paste the second function, looking at the count.

  4. Save the calculation. For each No, you should see the negative total. For each Yes you should see a zero.

  5. Now, you need to define the end point. So this will need to be the row count plus one, because each count starts with zero. You can copy and paste the same basic function above.

  6. Substitute the negative sign with a positive 1.

  7. Run and save the report. You'll get another calculation with the opposite inverse number plus one. 

Step five: Gathering the results

Now you can start gathering the results.

  1. Start with the offset_list function with brackets

  2. Next, in the brackets, search and add the value you're looking at, for example, the sales total.

  3. Include your Back calculation

  4. End with the Forward calculation.

  5. Your final calculation should look like:

  6. Save and run the calculation. In your report, on the right column you'll see a list of all the Sale Line totals. Each list should only be displayed on the last instance of each First Subcategory, and should reference only its respective totals.
  7. Lastly, create a sum of that group total.

  8. Save and run the calculation and report. In the report, where applicable, you'll get one total per First Subcategory.

Step six: Cleaning up calculations

There are some supportive calculations here that you shouldn't need for this report. You will use and need to keep:

  • First Subcategory
  • Back
  • Forward
  • Group Total
  • Same (at least once or twice more). 

You can remove everything else.

Your edited and cleaned up report should look cleaner.


 

Step seven: Looking for the last results

Now you can use the report to gather meaningful data. 

  1. To start, ask Analytics to return a 1 for every instance where "Same" is No, and return 0 otherwise. So start the calculation with brackets.

  2. Then, copy and paste the calculation for checking whether the First Subcategory is the same as the one below.

  3. Save and view the report, and you should see a 0 for each Yes, and a 1 for each No.

  4.  Next, to get these to rise in sequence, use the running_total function to return that number. Copy and paste this.

  5. copy and paste

  6. Save and view the report

  7. Next, to match the row of the report to the rising number enter: match(row(),)

  8. To make the row of the report match to the rising number copy and paste the calcultion from step 5.

  9. The final calculation should look like this:

  10. Save and run the report. Each row will return where the next First Subcategory can be found.

Step eight: Gathering labels and totals

Now, to complete the preparation of the data, you're going to use the Index function:

  1. Enter index()

  2. Enter the first subcategory label that you created.

  3. Then, from them, gather the indexed number.

  4. Copy and paste into your calculation.

  5. Save and run the report.

  6. This creates a singular entry for all the First Subcategories. Finally, do the same for the grouped totals

  7. Save and run the report. This is not the final version. Complete the next procedure to clean this report.

Step nine: Hiding the raw data

  1. Open the Table Visualization to view all the starter data.

  2. Only two of these columns are currently relevant. Hide the additional columns by clicking the Settings (Gear icon) in the column > Hide from Visualization.

  3. Keep the labels.

  4. Keep the grouped totals.

  5. Now when you view the visualization, you'll have a cleaner list of all the summary totals by first subcategory. 

Was this article helpful?

0 out of 0 found this helpful