help in a calculated expression with mix of AGGR, MAX and SUM

[zarmoton]

Hi to all,

really complex for me, SOS SOS !!!

PLEASE HELP TO FIND THE RIGHT EXPRESSION FOR "DIVISION" with this rule:

- Focus only on the sum of AMOUNT where TYPE_GRADE is the MAX value.

-if sum of amount only on max value for type_grade (by mydate and sitenumber) is egal to 0, we keep the lower value of type_grade, if different of 0, then division of all couple of mydate/site number is this max value of type_grade

I have done that but not working very well:

DIVISION=aggr(NODISTINCT max({<PROMOTION_CODE={"=SUM(AMOUNT)<>0"}>}TYPE_GRADE),DATE,SITE_NUMBER)

in red , what is not good

[zarmoton · Accepted solution]

I found it !

if (Sum({$<TYPE_GRADE = {"=aggr(NODISTINCT max(TYPE_GRADE), FCT_DATE, ACCOUNT_NUMBER)=TYPE_GRADE"}> } TOTAL <FCT_DATE, ACCOUNT_NUMBER> AMOUT_DIFF)<>0,
aggr(NODISTINCT max(TYPE_GRADE,1), FCT_DATE, ACCOUNT_NUMBER),
if (not isnull(aggr(NODISTINCT max(TYPE_GRADE,2), FCT_DATE, ACCOUNT_NUMBER)),
aggr(NODISTINCT max(TYPE_GRADE,2), FCT_DATE, ACCOUNT_NUMBER),
aggr(NODISTINCT max(TYPE_GRADE,1), FCT_DATE, ACCOUNT_NUMBER)) )