Qlikview Aggr() Net Price Calculation

[Anonymous]

Hi Team

I need to calculate Net Price, I have Net Sales And Net Volume fields available.

So I can calculate Net Price= [Net Sales]/[Net Volume]

Now I have to find the max net price value based on Region and Destination. For that I have written below expression. Which is working:

=Max(Aggr(Sum([Net Sales]/[Net Volume]),Region,Destination))

But now I have a requirement where I have to include only below combination of Net sales and Net Volume in my expression:

Net Sales ,Net Volume

+(values),+(values)

-(values),-(values)

Please suggest how can i modify my expressions for above combinations.

Thanks In Advance.

[sunny_talwar]

Sristi Upreti wrote:

Yes... Please help

What is wrong, can you elaborate?

[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 .

[sunny_talwar]

Why not try this?

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

[Anonymous]

Hi

PFA the dummy data and qvw.

The total should come as 343.2534.

[tresesco]

Your [Sales/Volume] field values don't match with the calculated values Sales/Volume (see the last column).

Expression gives the right result from the calculations (the right most column).

[sunny_talwar]

Try this

=Max(Aggr(If(Sign(Sum(Sales)) = Sign(Sum(Volume)), Sum([Sales/Volume])), Customer, Destination,Material,Region))