Difference between sum and expression Total

anuradhaa — Fri, 08 Aug 2014 09:38:34 GMT

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

Re: Difference between sum and expression Total

simenkg — Fri, 08 Aug 2014 09:44:51 GMT

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.

topic Difference between sum and expression Total in QlikView

Difference between sum and expression Total

Re: Difference between sum and expression Total