How do i find the Total Value of Sale/Qty

[#1]

Hi,

    I am not getting Below value(Red Color) by using this formula of Purchase Ton = Basic Value / Qty. If i use this formula i am getting my expression total(Blue color).

Cany any one pls help me to find the below value which is in Red  Color.

Date	GR_Num	Purchase_Item	Qty	Rate	Value	Basic Value	Pur Rate Ton
			74.98		1794445	1,858,004.89	72047.69
05-09-2011	3436	0101311	21.33	23,300.00	496989	532,194.24	24,950.50
09-09-2011	3553	0101311	36.47	24,600.00	897162	897,162.00	24,600.00
10-09-2011	3561	0101311	17.18	23,300.00	400294	428,648.65	24,950.45
						Out Put Will Be
Date	GR_Num	Purchase_Item	Qty	Rate	Value	Basic Value	Pur Rate Ton
			74.98		1794445	1,858,004.89	24,780.01
05-09-2011	3436	0101311	21.33	23,300.00	496989	532,194.24	24,950.50
09-09-2011	3553	0101311	36.47	24,600.00	897162	897,162.00	24,600.00
10-09-2011	3561	0101311	17.18	23,300.00	400294	428,648.65	24,950.45
						Pur Rate Ton = Basic	Value / Qty

Regards,

Nirmal.

[Anonymous]

The red looks like the average. Try changing the expression total to Sum of Rows instead.

EDIT: or Average of Rows if you're trying to get the red result. Can't tell for sure

[#3]

Hi Johannes,

Thanks for your reply.

        i tried but im not getting the above answer. i dont need the expression total. i want the Total Qty/ Total Basic Sale Value.

Thanks,

Nirmal.

[swuehl]

I think average of rows will not work here, since I believe Nirmal would need a weighted average here.

Nirmal, what are your expressions used for Basic Value and Qty? Could you post a small sample file?

[#5]

Can any help to me sort out this problem.....

[#6]

Hi Swuehl,

Qty =sum(if(gra_id=5,grd_rcv_qty - grd_rej_qty))

Basic Value =

Num(sum(if(gra_id=5,grd_rcv_qty - grd_rej_qty))*sum(if(gra_id=10,Gr_Rate)))

+ sum( if(Grd_Group='Insurance',Gr_Rate)) + sum( if(Grd_Group='Service Charge',Gr_Rate))

+

sum( if(Grd_Group='Freight',Gr_Rate)) +  sum( if(Grd_Group='Loading',Gr_Rate)) + sum( if(Grd_Group='Forwarding',Gr_Rate))  +

sum( if(Grd_Group='Other Charges',Gr_Rate)) - sum( if(Grd_Group='Other Discount',Gr_Rate)) - sum( if(Grd_Group='Discount',Gr_Rate))

[swuehl]

And you are not using sum-of-rows as total mode for Qty and/or Basic Value?

If you do, you probably need to use the technique sum-of-rows using advanced aggregation (there is a chapter in the Help about this), to calculate the sum of rows for numerator and denominator, for your Qty, it will look like

=sum(aggr( sum(if(gra_id=5,grd_rcv_qty - grd_rej_qty)), YOURCHARTDIMENSION))

[#8]

How do i use aggr for Basic value formula ?

[swuehl · Accepted solution]

Just follow the same pattern:

=sum( aggr( YOURCHARTEXPRESSION, YOURCHARTDIMENSIONS))

[#10]

Hi Swuehl,

Thanks a lot.....

Regards,

Nirmal.