Differences

This shows you the differences between two versions of the page.

--- transformations:subtotal [2018/07/22 16:00] – created dmitry
+++ transformations:subtotal [2021/05/05 10:47] – craigt
@@ Line 1: / Line 1: @@
-===== Total / subtotals =====
+{{ transformations:SubtotalAction.png}}
+====== TOTAL/SUBTOTALS ======
+Category: Transform / Math\\
-This action appends to a dataset a new column that contains an aggregated total for selected column, or subtotals for groups.
+\\
+=====Description=====
+This action appends to a dataset a new column that contains an aggregated total for selected column, or subtotals for groups.\\
-**Example 1**
+\\
+=====Use cases=====
+Create a "total" column to calculate a total or group percentage value with (e.g. total value/ group value).\\
-Source dataset:
+\\
+=====Action settings=====
+^Setting^Description^
+|New column|Enter a name for the new total/subtotal column.|
+|Target column|Select the numeric column used to generate the aggregate values.|
+|Mode|Select the aggregation mode.  Options:  //Sum//, //Count//, //Average//, //Min//, and //Max//.|
+|Groups|Select whether to derive the total/subtotal value across all rows in the dataset, or grouped by selected columns.\\  Options:  //Column total// or //Subtotal inside groups// (and select the columns to subtotal by).|
+\\
+=====Remarks=====
+The new column will appear as the rightmost column in the dataset, by default.
+The grouping aspect of the //Subtotal inside groups// option is case sensitive.  Values in the grouping column(s) having different cases will be calculated as unique values (e.g. not combined).\\
+\\
+=====Examples=====
+**Example 1:**  Add a column with Total Sales.\\
+**Source table:**
 ^ Region ^ Sales ^
 | North  |  100 |
@@ Line 11: / Line 35: @@
 | North  |  150 |
 | East   |  125 |
+\\
-Objective: add a column with Total Sales.
+**Action parameters:**
+> New column is "Total Sales"
-Resulting dataset:
+> Target column is "Sales"
+> Mode is "Sum"
+> Groups is "Column total"
+\\
+**Result:**
 ^ Region ^ Sales ^ Total Sales ^
 | North  |  100 |   575 |
@@ Line 21: / Line 49: @@
 | East   |  125 |   575 |
-**Example 2**
+\\
+\\
+**Example 2:**  Add a column with Total By Region.\\
-Source dataset:
+**Source table:**
 ^ Region ^ Sales ^
 | North  |  100 |
@@ Line 29: / Line 59: @@
 | North  |  150 |
 | East   |  125 |
+\\
-Objective: add a column with Total By Region.
+**Action parameters:**
+> New column is "Total by Region"
-Resulting dataset:
+> Target column is "Sales"
+> Mode is "Sum"
+> Groups is "Subtotal inside groups" (column "Region" selected)
+\\
+**Result:**
 ^ Region ^ Sales ^ Total By Region ^
 | North  |  100 |   250 |
@@ Line 39: / Line 73: @@
 | East   |  125 |   325 |
-**See also**
+\\
+=====See also=====
   * [[transformations:aggregate|Aggregate]]