Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Just follow the same pattern:
=sum( aggr( YOURCHARTEXPRESSION, YOURCHARTDIMENSIONS))
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
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.
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?
Can any help to me sort out this problem.....
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))
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))
How do i use aggr for Basic value formula ?
Just follow the same pattern:
=sum( aggr( YOURCHARTEXPRESSION, YOURCHARTDIMENSIONS))
Hi Swuehl,
Thanks a lot.....
Regards,
Nirmal.