Your example says, "If you select only one product, you will see the right value." But that seems wrong.
If I select ds_drug = x, then max(month_num) = 12. But since there's no connection between this month and the dt_consumption, we always sum all consumption for x regardless of the month. The same is true of selecting the other drugs individually - they always sum all consumption regardless of month.
In other words, if you DO see the right values if you select only one product, then this expression solves the problem:
sum(consumption)
It gives you the values you say you want.
I would GUESS that what you want is to instead only sum the consumption where the dt_consumption is in the given month regardless of year. If so, you need to fix the data model as a first step. You would want to replace this:
consumption2:
load cod_drug ,
month(dt_consumption) as month_drug,
Right(left(dt_consumption,5),2) as month_num,
year(dt_consumption) as year_drug
resident consumption;
With this:
LEFT JOIN (consumption)
LOAD
cod_drug
,dt_consumption
,month(dt_consumption) as month_drug
,num(month(dt_consumption),'00') as month_num
,year(dt_consumption) as year_drug
RESIDENT consumption;
I then guess you want actual results like this:
x has max month of 12, and we have one dt_consumption in that month, for 4789
y has max month of 5, and we have one dt_consumption in that month, for 1005
z has max month of 8, and we have two dt_consumption in that month, for 582 + 3065 = 3647
With the data model changes, you can then get those results like this:
aggr(if(month_num=max(total <ds_drug> month_num),sum(consumption)),ds_drug,month_num)
One way to see what that is doing is to turn the aggr() into a table. Your dimensions would be ds_drug and month_num. Add expressions for max(total <ds_drug> month_num) and sum(consumption). Hopefully you can then see how the aggr() works to produce the right result.
It's possible that there's a better solution, but that's what I came up with.
