This article describes the formula syntax and usage of the Matrix function in JungleDocs.
Achieves statistical data transformations using data from grouped lists.
Matrix(listName; viewName; [formula])
The Matrix function syntax has the following arguments:
listName Required. A SharePoint list name.
viewName Required. A SharePoint view name.
formula Optional. A formula that does calculations with items in a grouped view. If you omit this argument in the Matrix formula, the default function
Count($Items)will be applied to return the number of items. Instead of Count, you can also use the Sum, Average, Median, Min and Max functions (see example).
RecordSet Required. Any formula that returns a grouped view (e.g., GetView).
- You can learn more about entering JungleDocs formulas in Excel sheets here.
- You can also use formulas in Excel sheets to create Word charts.
To use Matrix with a grouped view
Let's say you have a grouped view, called Histogram, in the Tasks list.
Now, to transfer this view into an Excel sheet, enter the following formula in the Excel template cell
To use Matrix with 2-level grouping
The Matrix function also supports 2-level grouping. Let's say you have the Histogram view in the TimeSheet list:
in the Excel template, you can use Matrix to display the first level of grouping (year and month) as rows and the second level of grouping (person) as columns. Use this formula:
You can also apply further calculations to the data by adding commands to the formula. In the example, we want to Excel to return the sum of grouped items by the total field using the Sum function, so the final formula looks like this:
=Repeater(Matrix("TimeSheet"; "Histogram"; "Sum($Items;\"total")"))