Page Comparison

Question

The SUM function in the Datamart query below is not working as needed.

// Get datamart context
def dmContext = api.getDatamartContext()

// Create datamart query
def dmQueryInvoice = dmContext.newQuery(dmContext.getDatamart("Transactions"), false)
dmQueryInvoice.
select("INVOICE_NUMBER").
select("ACCOUNT_NAME").
select("SUM(FINAL_GIVEN_NET_PRICE)").
where(Filter.equal("INVOICE_DATEYear","2018"),
Filter.equal("BILLING_TYPE","F2"))

Answer

You need to add a variable name to the SUM statement to get the sum value from the query result:

select("SUM(FINAL_GIVEN_NET_PRICE)","SUM_FINAL_GIVEN_NET_PRICE")

Also, you should add separate WHERE clauses for each filter.

.where(Filter.equal("INVOICE_DATEYear","2018"))

.where(Filter.equal("BILLING_TYPE","F2"))

Also make sure that all DM fields used in SELECT statements are marked as dimension (in their underlying Data Source).

The second parameter of the method newQuery​(Table table, boolean rollup) is important - if its value is FALSE, then there will be no rollup/aggregation happening.

So when you set the rollup param to TRUE, the rollup will happen (i.e., the SUM function will work).