Nested Aggr() expression in Table.

[infosense_devel]

Hello All,

i have scenario as below.

i have two table as shown in below image.

For "First" table i used below expression.

Aggr(sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC)

and for "Second" table i used below expression.

(Aggr(Max((Aggr(sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}

RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC))),ITEM_NO))

what i want to do here is, i want to take Max Sales line but if you observe "First" and "Second" table closely in second table that "Division_Desc" column is wrong because in "First" table we have "CONSUMER" but in second table somehow it is taking "INDUSTRIAL".

Desire Result:-

i need final result as

ITEM_NO     DIVISION_DESC         SALES

90081            CONSUMER             988,606

Thanks in Advance.

[sunny_talwar]

May be try this

If(Max(TOTAL <ITEM_NO> Aggr(Sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC) = sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE))

[infosense_devel]

Thanks for reply,

i have paste below expression as per your suggestion, but it is not returning any row.

If(Max(TOTAL <ITEM_NO> Aggr(Sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC))

= sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE),

sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE))

can you please help?

[sunny_talwar]

Lets break it down

1) Max(TOTAL <ITEM_NO> Aggr(Sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC))

Should give you 988,606 for all the rows

2) sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE)

Should give individual sums for each row

3) If(Max(TOTAL <ITEM_NO> Aggr(Sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC)) = sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), 1, 0)

Should give you 1 for the max row and 0 for all others

4) If(Max(TOTAL <ITEM_NO> Aggr(Sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC)) = sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), 0)

Should give you the max on the max row and give 0 otherwise...

Can you post the screenshot of what you get using the above 4 expressions

[infosense_devel]

Hello Sunny,

First two are behaving as you mentioned but Third Expression is not returning any row at all .

[sunny_talwar]

I asked all four expression in a single chart? What is the above screenshot? Don't understand where was the miss communication my friend?

[infosense_devel]

Sorry for that,

Please find below screenshot with all four exp. in one chart.

[sunny_talwar · Accepted solution]

May be a rounding problem... try this for the third expression and see what you get

If(

Floor(Max(TOTAL <ITEM_NO> Aggr(Sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE), ITEM_NO,DIVISION_DESC)))

=

Floor(sum({< DIVISION_DESC-={'TOTAL GROUP'},Accounts={'Gross sales'},REC_TYPE={InvoiceDateRec}>}RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE))

, 1, 0)

[infosense_devel]

Yes, now its is working as per expected.

Thanks a lot Sunny. you are champ.