Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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 - Creator
Partner - Creator

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