year aggregation different from sum of month aggregation

[bertrand01]

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 ?

[JonasValleskog]

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