Using aggregate functions in a map expression in Compose

jtompkins — Fri, 14 Mar 2025 20:49:07 GMT

Hello,

I am trying to use a simple sum() function in one of my Compose maps as an expression. When I go to the Functions tab in the "Create Expressions" menu in the map, I found the following:

SUM({distinct|all} ${expr}) OVER({analytic_clause})

Hovering over this, it says it can be used as an aggregate or an analytic function. How do I use this as an aggregate function? Where do I put my group by clause? Where do I put the field name I want to return the sum of?

Thanks.

Re: Using aggregate functions in a map expression in Compose

TimGarrod — Mon, 10 Aug 2020 20:07:09 GMT

The aggregate functions presented in Compose expression editors (for standard mappings to the DW model) are know as Window-ed analytic functions / windowed aggregates. Windowed analytical functions do not leverage a GROUP BY as they allow you to provide a set of data lower than the aggregate grain (the group by).

A simple example from a query perspective would be a requirement to display all sales, with a sum of the total revenue for the entire day..

e.g. - Select Date, Product, Acct, Qty, SUM(Quantity) OVER (Partition by Date) as DailyTotal from X

In this instance the PARTITION BY clause provides the WINDOW (acts similar to a group by) - but it is over the entire set of data. The data is not provided at the [DATE] grain, but the aggregate is.

I recommend looking in the documentation for your data warehouse platform for Windowed Aggregates to understand what features are available in the dw platform as Compose will adhere / support what the platform provides.

If you're goal is to have a "simple" aggregate, Compose provides aggregated fact table support. (It is always a best practice in a DW to store data at the lowest granularity in the modeled layer and aggregate in the data marts). Having said that - there are commonly requirements to calculate and store an aggregate. If you are processing in batch, and wish to store / calculate an aggregate it is recommended to use query as the source for the mapping.

Hope this helps.

topic Re: Using aggregate functions in a map expression in Compose in Qlik Compose

Using aggregate functions in a map expression in Compose

Re: Using aggregate functions in a map expression in Compose