Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
bertrand01
Contributor III
Contributor III

year aggregation different from sum of month aggregation

Hi everybody, in the same graph, I can aggregate data by Year/Quarter/Month

And year aggregation is different from sum of month aggregation.

After investigating, the problem comes from the formula in the graph, to simplify,
let's say : formula=sum(a)/sum(b).
So for the first quarter for exemple, the automatic sum for the quarter will be equivalent to (a1+a2+a3)/(b1+b2+b3) which is different from the sum of the 3 first months : (a1/b1)+(a2/b2)+(a3/b3).

Hope my question is clear enough.
Is there a way to solve this problem ? Any suggestions ?

Labels (1)
1 Reply
JonasValleskog
Partner
Partner

Hi there!

I'm curious what your use case is for adding ratios up?

What you are effectively after is a multi step aggregation - like a sum of averages or average of sums, only this time it is a sum of ratios where the ratio is calculated a month grain and then straight summed up. In Qlik, this is achieved by the Aggr() statement. A function that allows for operations similar to that of a SQL GROUP BY statement if you are familiar with SQL.

I've attached a fully worked example to aid your studies, but if you just need an example expression - the one I used in my example app looks like this: sum(aggr(sum(Value)/sum(Cost),Year,Quarter,Month))

Regards

-Jonas