topic Re: help on expressions(aggr) in QlikView

help on expressions(aggr)

Sun, 07 Jun 2015 18:03:48 GMT

Guys need help with the following-

Inv No Qty charges vol current output required col

a1 2 5 1 - (5*2)/(2*1+2*2+3*1+2*2)

a1 2 10 2 - (2*10)/(2*1+2*2+3*1+2*2)

a1 3 5 1 - (3*5)/(2*1+2*2+3*1+2*2)

a1 2 10 2 (2*10)/(2*1+2*2+3*1+2*2) (2*10)/(2*1+2*2+3*1+2*2)

a2 1 5 2 (1*5)/(1*2) (1*5)/(1*2)

a3 2 10 2 - (2*10)/((*2+3*1)

a3 3 5 1 (3*1)/(2*2+3*1) (3*1)/(2*2+3*1)

I am getting the current output but few rows are '-'.

I am using aggr function,the expression I am using is as follows - (freight*qty)/aggr(sum(Qty*vol),inv no).

Aggr function is grouping by inv no and hence I am getting output only for the last row of the same Inv no.

i need urgent help.

Appreciate your suggestions.

swuehl — Sun, 07 Jun 2015 18:41:35 GMT

I think you can replace the advanced aggregation with a TOTAL qualifier with field list:

=(freight*qty) / sum(TOTAL<[Inv No]> Qty*vol)

You should also consider using aggregation functions around all fields in your expression:

=sum(freight*qty) / sum(TOTAL<[Inv No]> Qty * vol)

maxgro — Sun, 07 Jun 2015 18:53:00 GMT

PFA

ToniKautto — Sun, 07 Jun 2015 22:47:41 GMT

Please notice that Aggr() is distinct by default. Add the NODISTINCT keyword in the Aggr() function to expand the result to all rows.

Aggr(NODISTINCT Expression, Dim1)

As suggested above the Aggr() function might not be needed in your expression in order to resolve your aggregation.