topic Re: Qlikview Aggr() Net Price Calculation in QlikView

Qlikview Aggr() Net Price Calculation

Anonymous — Mon, 27 Nov 2017 15:56:23 GMT

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.

Re: Qlikview Aggr() Net Price Calculation

vishsaggi — Mon, 27 Nov 2017 16:00:46 GMT

Can you elaborate what you mean by +Values and -Values, Can you share some sample data with your expected output. ?

Re: Qlikview Aggr() Net Price Calculation

sunny_talwar — Mon, 27 Nov 2017 16:10:06 GMT

May be this

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

Re: Qlikview Aggr() Net Price Calculation

Anonymous — Mon, 27 Nov 2017 16:11:44 GMT

Hi PFA image.

Re: Qlikview Aggr() Net Price Calculation

vishsaggi — Mon, 27 Nov 2017 16:50:57 GMT

Can we write this way too?

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

Re: Qlikview Aggr() Net Price Calculation

sunny_talwar — Mon, 27 Nov 2017 16:57:55 GMT

But what about the negative condition? Meaning when both are negative?

Re: Qlikview Aggr() Net Price Calculation

vishsaggi — Mon, 27 Nov 2017 17:06:08 GMT

Oh Yeah. Thats correct going to be a long if condition.

Re: Qlikview Aggr() Net Price Calculation

sunny_talwar — Mon, 27 Nov 2017 17:19:14 GMT

Yes

Re: Qlikview Aggr() Net Price Calculation

Anonymous — Tue, 28 Nov 2017 07:31:14 GMT

Yes... Please help

Re: Qlikview Aggr() Net Price Calculation

vishsaggi — Tue, 28 Nov 2017 14:26:46 GMT

Did you try what Sunny suggested? If not can you try and let us know where you having problems.

Re: Qlikview Aggr() Net Price Calculation

sunny_talwar — Tue, 28 Nov 2017 23:34:47 GMT

Sristi Upreti wrote:

Yes... Please help

What is wrong, can you elaborate?

Re: Qlikview Aggr() Net Price Calculation

Anonymous — Wed, 29 Nov 2017 07:53:10 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() Net Price Calculation

sunny_talwar — Wed, 29 Nov 2017 07:56:11 GMT

Why not try this?

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

Re: Qlikview Aggr() Net Price Calculation

Anonymous — Wed, 29 Nov 2017 08:55:03 GMT

PFA the dummy data and qvw.

The total should come as 343.2534.

Re: Qlikview Aggr() Net Price Calculation

tresB — Wed, 29 Nov 2017 09:26:40 GMT

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

Re: Qlikview Aggr() Net Price Calculation

sunny_talwar — Wed, 29 Nov 2017 13:15:49 GMT

Try this

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