Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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)) )

 

View solution in original post