SI.QUERY with Grouping
Created by Harry Lewis, Modified on Thu, 4 Jan at 7:29 PM by Harry Lewis
Applies to:
- Velixo NX
- Sage Intacct
Overview
The Velixo SI.QUERY function for Sage Intacct supports the ability to perform grouping and aggregation of data.
TABLE OF CONTENTS
Examples
Assuming the following results of an SI.QUERY showing - for each project - the PROJECTTYPE and the ACTUALQTY fields (using 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
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article