SI.QUERY with aggregated measures

pplies to:

• Velixo NX
• Sage Intacct

Overview

The Velixo SI.QUERY function for Sage Intacct supports the ability to perform aggregation of data and the data can be sorted to provide grouping.

TABLE OF CONTENTS

• Overview
• Examples
  • Example 1 - aggregate a single field
  • Example 2 - multiple aggregations for a single field
  • Example 3 - aggregations on multiple fields
  • Example 4 - aggregation with multiple levels of grouping

Examples

Assuming the following results of an SI.QUERY function showing the PROJECTTYPE, ACTUALQTY, BUDGETQTY, and MANAGERCONTACTNAME fields from Sage Intacct's PROJECT object:

Example 1 - aggregate a single field

SI.QUERY("Sage","Project",, "PROJECTTYPE, SUM(ACTUALQTY)")

Description

Displays a sum of ACTUALQTY grouped by PROJECTTYPE  

Result

Example 2 - multiple aggregations for a single field

SI.QUERY("Sage","Project",, "PROJECTTYPE, SUM(ACTUALQTY), AVG(ACTUALQTY)")

Description

Displays a sum and average of ACTUALQTY grouped by PROJECTTYPE  

Result

Example 3 - aggregations on multiple fields

SI.QUERY("Sage","Project",, "PROJECTTYPE, SUM(ACTUALQTY), SUM(BUDGETAMOUNT)")

Description

Displays a sum of both ACTUALQTY and BUDGETAMOUNT grouped by PROJECTTYPE  

Result

Example 4 - aggregation with multiple levels of grouping

=SORT(SI.QUERY("Sage","PROJECT",,"MANAGERCONTACTNAME,PROJECTTYPE,SUM(ACTUALQTY)",FALSE),{1,2,3},1)

Description

Displays a sum of ACTUALQTY grouped first by MANAGERCONTACTNAME and then by PROJECTYPE.  The Excel SORT function is used to combine the grouped aggregations.

Result

You can change the order of the fields being retrieved in order to change how the data is grouped.

=SORT(SI.QUERY("Sage","PROJECT",,"PROJECTTYPE, MANAGERCONTACTNAME, SUM(ACTUALQTY)",FALSE),{1,2,3},1)

Description

Displays a sum of ACTUALQTY grouped first by PROJECTYPE and then by MANAGERCONTACTNAME. 

Result