topic Qlikview Aggr Function max in QlikView

Qlikview Aggr Function max

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Qlikview Aggr Function max

Anil_Babu_Samineni — Tue, 28 Nov 2017 10:32:08 GMT

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

Re: Qlikview Aggr Function max

Anonymous — Tue, 28 Nov 2017 10:40:34 GMT

This expression is not working for me.

Re: Qlikview Aggr Function max

Anil_Babu_Samineni — Tue, 28 Nov 2017 10:43:08 GMT

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

Re: Qlikview Aggr Function max

Anonymous — Tue, 28 Nov 2017 10:53:45 GMT

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

Re: Qlikview Aggr Function max

Anil_Babu_Samineni — Tue, 28 Nov 2017 11:11:49 GMT

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

Re: Qlikview Aggr Function max

Anil_Babu_Samineni — Tue, 28 Nov 2017 11:13:48 GMT

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

Re: Qlikview Aggr Function max

Anonymous — Wed, 29 Nov 2017 07:53:32 GMT

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 .

Re: Qlikview Aggr Function max

Anil_Babu_Samineni — Wed, 29 Nov 2017 08:26:46 GMT

Max() always returns last row i believe.

Re: Qlikview Aggr Function max

Anonymous — Wed, 29 Nov 2017 09:08:30 GMT

PFA excel and qvw.

Re: Qlikview Aggr Function max

Anonymous — Wed, 29 Nov 2017 09:46:34 GMT

Hi,

PFA. Is that you looking for?

-Jai

Re: Qlikview Aggr Function max

jonathandienst — Wed, 29 Nov 2017 09:59:12 GMT

The answer Sunny gave you on the previous thread you posted on this topic will precisely what you need,