Matrix Column and Formula
Last updated
Last updated
In Mashmatrix Sheet formulas, it is possible to refer to child columns and series values in a matrix column as column variables, just as regular columns. It is also possible to use a formula in a child column of a matrix column and display the calculated result within the series.
For regular columns, the notation [@Column Name] is used within the formula text to represent Column Value (the value of the column in the record where the formula is currently being evaluated), or [#Column Name] is used to represent Column Reference (the entire column contained in the sheet) as a variable. For child columns of a matrix column, following notations are used to represent variables that refer to columns.
Column Value
[Matrix Column Name].[Series Column Name].[@Child Column Name]
Column Reference
[Matrix Column Name].[Series Column Name].[#Child Column Name]
The references named Matrix Column Name, Series Column Name, and Child Column Name can be confirmed from the column setting screen of each column types. The Series Column Names are automatically assigned in order with index numbers beginning with 1 in the form $1, $2, $3.
When referencing child columns of the matrix columns on the other sheet as column reference variables, it should be prefixed with the sheet ID of the sheet.
[Sheet ID].[Matrix Column Name].[Series Column Name].[#Child Column Name]
Visual Understanding
The following image shows a visual representation of what each column variable represents in the evaluation of a formula for a record when there are columns Planned__c and Actual__c as child columns in the matrix column SalesPlans__r.
In the formula editor, the child columns are displayed in a hierarchical tree for each series values in the matrix columns. Insert variables within the formula text by selecting the appropriate series values' child column.
When referring from a formula inside a matrix column to an child column of the same matrix column, it is possible to refer to a child column contained in a series column that is relative to the series column containing the formula currently being evaluated, rather than specifying a particular series column.
Column Value
[Matrix Column Name].[Relative Series Position].[@Child Column Name]
Column Reference
[Matrix Column Name].[Relative Series Position].[#Child Column Name]
Following values can be set in Relative Series Position:
$ - Series column where the formula is evaluated (Current Series)
$-n - Series column n steps ahead of the current series (n is a positive integer value)
$+n - Series column n steps after the current series (n is a positive integer value)
If a relative series position is specified and the reference exceeds the range of the series in the current matrix column, the value of the variable is treated as a blank value.
Visual Understanding
The following image shows a visual representation of what each column variable represents in the evaluation of a formula in a child column in the second series of a record when there are columns Planned__c and Actual__c as child columns in the matrix column SalesPlans__r.
When editing formulas inside a matrix column, the formula editor displays a list of child columns in the matrix column hierarchy tree. After selecting the appropriate relative position from the series node drop-down menu, select the child column and insert the variable in the formula text.
In the formula, a series value in a matrix column can be referenced as a variable. The series value is the date value of the start date in the respective time series. Within the formula text, the notation is of the following form.
[Matrix Column Name].[@Series Column Name]
For example, if the matrix column SalesPlans__r is set to display matrix transforms at monthly intervals starting on Jan 1, 2020, the date values returned by each column variable are as follows:
[SalesPlans__r].[@$1] => "2020-01-01"
[SalesPlans__r].[@$2] ⇒ "2020-02-01"
[SalesPlans__r].[@$3] ⇒ "2020-03-01"
[SalesPlans__r].[@$4] ⇒ "2020-04-01"
...
For formulas inside the same matrix column, relative notation is available for the reference of the series column. The values that can be specified in the relative series position are the same as for the child columns.
[Matrix Column Name].[@Relative Series Position]
For example, if the matrix column SalesPlans__r is set to display matrix transforms at monthly intervals starting on Jan 1, 2020, the date values returned by each column variable when the formula is evaluated within the series Apr 2020 are as follows:
[SalesPlans__r].[@$] ⇒ "2020-04-01"
[SalesPlans__r].[@$-1] ⇒ "2020-03-01"
[SalesPlans__r].[@$-2] ⇒ "2020-02-01"
[SalesPlans__r].[@$+1] ⇒ "2020-05-01"
[SalesPlans__r].[@$+2] ⇒ "2020-06-01"
...
Reference in Formula Editor
When editing formulas inside a matrix column, references to series values appear in the hierarchical tree of the matrix column.
For relative references, select the appropriate relative position from the series node drop-down menu, then select the "(Series Value)" node to insert the variable within the formula text.
In the following example formulas, assume that there is a sheet s1 that displays a list of products, and has a matrix column named SalesPlans__r , set to display 12 months of data with a monthly interval starting on Jan 1, 2020. Additionally, a currency type columns storing planned and actual amounts with names Planned__c and Actual__c are added as child columns of the matrix column SalesPlans__r.
Add a formula column in the sheet, of type Currency, with the following formula text. Within the formula, all the series in the matrix column are specified to refer to and add together the planned amount values.
Add a formula column as a child column of the matrix column SalesPlans__r, of type Currency, with the following formula text. Within the formula, the actual and planned amounts for the current series are referenced and the difference is calculated.
Add a formula column as a child column of the matrix column SalesPlans__r, of type Text, with the following formula text. Within the formula, the actual amount of the current series and the previous series are referenced, and the difference is calculated, returning "+" if the difference is positive and "-" if it is negative. In the blank field handling setting of the formula, check the "Treat blank fields as blanks" checkbox.
First, reconfigure the matrix column SalesPlans__r to display 24 months with a start date of Jan 1, 2019, and hide the first series from Jan 2019 through Dec 2019.
Then, add a formula column as a child column of the matrix column SalesPlans__r, of type Currency, with the following formula text. The formula refers to the actual amount 12 steps (= 12 months) prior to the current series.
add a formula column as a child column of the matrix column SalesPlans__r, of type Percent, with the following formula text. The formula refers to the actual amount 12 steps (= 12 months) prior to the current series. Within the formula, the sum of the planned amounts within the same series is calculated, and the planned amount for the current record is divided by that total amount to calculate the ratio of the total amount.
