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. 

Syntax

COLLECT()

Arguments

The COLLECT function does not use any arguments.

This function returns numbers.

Additional information

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.

Related Anaplan functions

LOOKUP

Examples

General example

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:


FY15
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:


FY15
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 
      Margin %
      Cost of Sales
   Gross Margin
      Headcount
      Cost per employee
      Staff Costs
      Rent & Rates
      Utilities
      Marketing
      IT costs
      Total Overheads
   Operating Profit
Fixed assets
      Assets Sold
      Buildings
      Fixtures
      Software
      Patents
      Assets Purchased

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

Line item subset example

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

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.