Finding Percentage In Table Box

[Anonymous]

Hi All,

In this table I have calculated Stock In Hand, Pending Order, Unsold Qty, Sold Qty and trying to calculate unsold percentage and sold percentage i.e Unsold Qty/ Stock In Hand

Formula for Unsold Qty is  

if(((Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}Value))-

(Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}[Pending Order])))>0,

((Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}Value))-

(Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}[Pending Order]))))

and formula for Stock In Hand is

(Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}Value))

I am getting right values in the cells but Total value on the header is showing wrong information.

Please help me in correcting it.

[tresesco]

One quick correction you can try like:

Wherever you used:    date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy')   

replace it with :            date(today()-4,'dd/MM/yyyy')  

i.e. - remove date#() and use 'MM' instead of 'mm'

[Anonymous]

This doesn't makes any difference , I think I need to use Aggr() function but how I need to use it here i am not understanding.

[tresesco]

At least changing 'mm' to 'MM' should have given you a changed figure if even not the correct one. However, to come to your point about total mismatch, you could try like:

Sum(Aggr(<your existing exp>, Dim1, Dim2..))               // Dim1, Dim2... - are your chart dimensions

[Anil_Babu_Samineni]

May be this?

Sum(Aggr(

if(((Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}Value))-

(Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}[Pending Order])))>0,

((Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}Value))-

(Sum({<Common_Date={'$(=date(date#(today(),'mm/dd/yyyy')-4,'dd/mm/yyyy'))'}>}[Pending Order])))), Date, Item_NOC))