topic Re: Nested Aggr() expression in Table. in QlikView

Nested Aggr() expression in Table.

infosense_devel — Thu, 28 Sep 2017 14:28:54 GMT

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.

Re: Nested Aggr() expression in Table.

sunny_talwar — Thu, 28 Sep 2017 14:34:44 GMT

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

Re: Nested Aggr() expression in Table.

infosense_devel — Thu, 28 Sep 2017 15:18:39 GMT

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?

Re: Nested Aggr() expression in Table.

sunny_talwar — Thu, 28 Sep 2017 15:23:23 GMT

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

Re: Nested Aggr() expression in Table.

infosense_devel — Thu, 28 Sep 2017 15:31:42 GMT

Hello Sunny,

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

Re: Nested Aggr() expression in Table.

sunny_talwar — Thu, 28 Sep 2017 15:34:34 GMT

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

Re: Nested Aggr() expression in Table.

infosense_devel — Thu, 28 Sep 2017 15:39:52 GMT

Sorry for that,

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

Re: Nested Aggr() expression in Table.

sunny_talwar — Thu, 28 Sep 2017 15:43:19 GMT

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)

Re: Nested Aggr() expression in Table.

infosense_devel — Thu, 28 Sep 2017 15:47:47 GMT

Yes, now its is working as per expected.

Thanks a lot Sunny. you are champ.