...
...
...
...
...
...
...
...
Question
I have a Datamart named "SalesHistoryDM" with the following columns:
Sku
CustomerId
YearMonth (Time Dimension)
Revenue
UnitsSold
MarginalContributionAbs
MarginalContributionPerc
...
I need to aggregate the Datamart in the logic for a specific product. I need to to sum Revenue, UnitsSold, MarginalContribution and count how many customers bought the product per year.
Answer
You need to run a Datamart query:
| Code Block |
|---|
def filters = [ Filter.equal("Sku", sku), ] def dmCtx = api.getDatamartContext() def salesDM = dmCtx.getTable("SalesHistoryDM") def datamartQuery = dmCtx.newQuery(salesDM) datamartQuery.select("SUM(Revenue)", "revenue") datamartQuery.select("SUM(UnitsSold)", "unitsSold") datamartQuery.select("SUM(MarginalContributionAbs)", "marginAbs") datamartQuery.select("COUNTDISTINCT(CustomerId)", "customersCount") datamartQuery.select("YearMonthYear", "year") datamartQuery.where(filters) def result = dmCtx.executeQuery(datamartQuery)?.getData()?.collect() ?: [] |
In the result you will get a list of maps:
| Code Block |
|---|
[
[ revenue: 123.0, unitsSold: 2, marginAbs: 56.78, customersCount: 2, year: 2017],
[ revenue: 456.0, unitsSold: 4, marginAbs: 123.45, customersCount: 3, year: 2018]
] |