average

Wed, 12 Aug 2009 03:46:32 GMT

Hi,

I have a transaction table with this fields:

Transaction:

Year,

Month,

ProductCode,

Price,

Cost,

Quantity,

I need to calculate The Average (considering de quantity of de sell)

F.E. : if I have two transactions with diferent prices :

Productcode price cost quantity Amount

A 130,79 105,64 16 2092,64

A 127,26 105,64 2 254,52

The average i need to calculate is this:

(Total Amount: 2347,16 / Total Quantity: 18 = ) (130,39 -105,64) /130,39 = 0,18987

I`ve tried to calculate this in the following script but qlikview doesn`t finish the loading:

Load:

Year,

Month,

ProductCode,

((Sum( Price*Quantity)/sum(quantity)) -avg(Cost)) /

(Sum( Price*Quantity)/sum(quantity)) as Average

resident Transaction

group by Year,Month, ProductCode;

Can anyone help me?

Thanks!

average

johnw — Thu, 13 Aug 2009 01:09:57 GMT

I'm not why it wouldn't load. Maybe it has a problem with the avg(). My first step would be to simplify the math and get rid of that part at the same time.

((sum(Price*Quantity)/sum(Quantity))-avg(Cost))/(sum(Price*Quantity)/sum(Quantity))
= ((sum(Price*Quantity)/sum(Quantity))-(sum(Cost*Quantity)/sum(Quantity))/(sum(Price*Quantity)/sum(Quantity))
= (sum(Price*Quantity)-sum(Cost*Quantity))/sum(Price*Quantity)
= 1 - sum(Cost*Quantity)/sum(Price*Quantity)

My next step would be to not precalculate the margin in the script. The margin depends on dividing sums, and therefore can't just be calculated for every row. You can calculate for groupings of fields in the script, which is what you seem to be trying to do, but there is no need. It is MUCH more flexible to just do the calculations in chart expressions, so that you can add and remove dimensions (including generating subtotals) without needing to reload your data and store an additional table.

See attached.

topic average in QlikView

average

average