Use the COLLECT function in a module that includes a line item subset to pull the source line item values into the module.

For example, you might want to compare 2 months and view the variance between those months for different line items.

To create a comparison, create a line item subset to group line items from different modules in one place. Then, create a staging module and pull the data from the line items into the module using the COLLECT function. 

COLLECT()

The COLLECT function does not use any arguments.

This function returns numbers.

The source modules must contain the line items in the line item subset used in the result module. The result module must have a line item subset as a dimension.

See Line item subset example for more information.

The function's behavior may change if there’s a mismatch in list hierarchy for the applied dimensions of line items within a line item subset.

The COLLECT function can be used to collect values from a line item subset. In this example, the two source modules for the line item subset are named P&L and Fixed assets. The aggregated total for FY22 from the Time dimension displays as the single column in both modules, and line items on rows.

P&L module:


FY22
Sales last year1200000
% increase5%
Sales1260000
Margin %40%
Cost of Sales756000
Gross Margin2016000
Headcount10
Cost per employee
Staff Costs360000
Rent & Rates12000
Utilities12000
Marketing12000
IT costs12000
Total Overheads408000
Operating Profit1608000

Revenue module:


FY22
Assets Sold720000
Buildings120000
Fixtures120000
Software120000
Patents120000
Assets Purchased480000

The line items selected for the line item subset display as below within Line item subsets . The line item subset is named Receipts and Payments.


Receipts and Payments
P&L
      Sales last year
      % increase
      Sales 

true

      Margin %
      Cost of Sales

true

   Gross Margin

true

      Headcount
      Cost per employee
      Staff Costs

true

      Rent & Rates

true

      Utilities

true

      Marketing

true

      IT costs

true

      Total Overheads

true

   Operating Profit

true

Fixed assets
      Assets Sold

true

      Buildings

true

      Fixtures

true

      Software

true

      Patents

true

      Assets Purchased

true

The above line item subset is used for the rows dimension in a Receipts and Payments module. FY22 from the Time dimension is on columns, and a single line item is on the pages dimension. The line item contains this formula: COLLECT(). This collects the values from the P&L and Fixed Assets source modules:


FY22
Sales1260000
Cost of Sales756000
Gross Margin2016000
Staff Costs360000
Rent & Rates12000
Utilities12000
Marketing12000
IT costs12000
Total Overheads408000
Operating Profit1608000
Assets Sold720000
Buildings120000
Fixtures120000
Software120000
Patents120000
Assets Purchased480000

The Variance report staging module step of the Line item subset example contains an example of how you can use the COLLECT function.