SI.QUERY with aggregated measures

Created by Harry Lewis, Modified on Mon, 23 Sep at 12:29 PM by Harry Lewis

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



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



Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article