Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zarmoton
Creator
Creator

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

 

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 goodin red , what is not good

 

Screen Shot 05-05-20 at 11.43 AM.PNGScreen Shot 05-05-20 at 11.45 AM.PNG

1 Solution

Accepted Solutions
zarmoton
Creator
Creator
Author

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)) )

 

View solution in original post

1 Reply
zarmoton
Creator
Creator
Author

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)) )