Qlikview Aggr Function max

[Anonymous]

Hi Team

Need Help in getting a result.

PFA image.

I have a requirement where I need to display net price. I have written below expression s:

= Max(Aggr(Sum(If( [Net Sales] > 0 AND [Net Volume] > 0, [Net Sales] /[Net Volume] )), Region, Destination))

for considering +values .

= Max(Aggr(Sum(If( [Net Sales] < 0 AND [Net Volume] < 0, [Net Sales] /[Net Volume] )), Region, Destination))

for considering - values.

I want only one expression for both the cases.

Please suggest

Thanks In Advance

[Anil_Babu_Samineni]

May be this?

Max(Aggr(Sum({<[Net Sales] = {"=[Net Sales] >0 and [Net Sales] < 0"}, [Net Volume] = {"=[Net Volume] >0 and [Net Volume] < 0"}>} ([Net Sales] /[Net Volume])), Region, Destination))

[Anonymous]

This expression is not working for me.

[Anil_Babu_Samineni]

Is this not giving correct output for you?

= Max(Aggr(Sum(If( [Net Sales] > 0 AND [Net Volume] > 0, [Net Sales] /[Net Volume] )), Region, Destination)) + Max(Aggr(Sum(If( [Net Sales] < 0 AND [Net Volume] < 0, [Net Sales] /[Net Volume] )), Region, Destination))

Or, Simply checking whether it is possible. May be try a luck

= Max(Aggr(Sum(If( ([Net Sales] > 0 AND [Net Volume] > 0) and ([Net Sales] < 0 AND [Net Volume] < 0), [Net Sales] /[Net Volume] )), Region, Destination))

[Anonymous]

Both expressions are not Working. Giving 0 as a result.

[Anil_Babu_Samineni]

Last luck from my end.

Max(Aggr(Sum({<[Net Sales] = {"=[Net Sales] = ([Net Sales] >0 and [Net Sales] < 0)"}, [Net Volume] = {"=[Net Volume] = ([Net Volume] >0 and [Net Volume] < 0)"}>} ([Net Sales] /[Net Volume])), Region, Destination))

If none of them not working, I would expect application to investigate

[Anil_Babu_Samineni]

And in my last-last reply i believe 1st expression should work. Can you check in separate text box and paste image to each together. Will test accordingly

[Anonymous]

I have written below expression:

=Max(Aggr(Sum({<[Net Sales] = {"=[Net Sales] >0 or [Net Sales] < 0"},

[Net Volume] = {"=[Net Volume] >0 or [Net Volume] < 0"}>} ([Net Sales] /[Net Volume])), Region,Destination))

Suppose I have selected 5 rows. Then it is giving me the result of last selected row record .

[Anil_Babu_Samineni]

Max() always returns last row i believe.

[Anonymous]

PFA excel and qvw.