Difference between sum and expression Total

[anuradhaa]

Hi All,

I use below expression to calculate charges. It has below logic

=money((if(OWNER='ABC' or OWNER='XYZ',0,

(if(((OWNER='YYY' or OWNER='ZZZ') and (Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')))

,sum(YYY_Total),(if((OWNER='YYY'),sum(YYY_Total),(if((Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')),

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),

((((sum(COUNTA)-13000)*.083)+1300)*190.6),((((sum(COUNTA)-22000)*.071)+2047)*190.6))))) ,

((if((sum(COUNTA)<=13000),((sum(COUNTA)*.1010)*190.6),if((sum(COUNTA)>13000 and  sum(COUNTA)<=22000),((((sum(COUNTA)-13000)*.0838)+1313)*190.6),

((((sum(COUNTA)-22000)*.0715)+2067.2)*190.6))))))))))))))

So I use this in straight Table to get separate values based on OWNER,

So the individual values are correct according to logic, but the Total is incorrect when i select expression total.

Here i use same expression in text object to get total sum of values. So i need to get actual sum of all owners, but now the display value is incorrect. please help me to solve this and get correct Total.

Thanks

[simenkg]

You are better off fixing your data model before starting to tackle that beast of an expression. This is neither scalable nor maintainable.

In the total calculation you have multiple values for OWNER and MonthYear so you can't call them without an aggregation function.

Surround your whole expression with Sum(Aggr(EXPRESSION,OWNER,MonthYear)) and you should get an answer.