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 ?
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))
