I am creating a waterfall chart to display the movement of product inventory level for the next 3 months, upon arriving shipments.
I have arriving shipments on May-2012 and Jun-2012, but I only want to get the summation of [Shipmt Qty] for [Shipmt ETA]>Today(), for the second expression or bar of my waterfall chart, so that I can get the end of month incoming quantity. I have expressed an expression for it, and successfully get the summation of [Shipmt Qty] for [Shipmt ETA]>Today(). (I have created the chart on 28-Jun-2012, that is why the expression only grabs the [Shipmt ETA] of 29-Jun-2012)
The data set is not complete yet. I may have arriving shipments on July, August, and so on. The second bar is the Jun-2012 end of month arriving quantity. Then I will have the third bar for July-2012, and the fourth bar for Aug-2012. In that case, the expression I have expressed is not completed yet. It only grabs the [Shipmt ETA]>Today(), but without restricted the [Shipmt ETA]s to the respective month. It will sum the [Shipmt Qty] for July and Aug also.
Below is the expression. Anyone can help to further complete the expression? Is set analysis able to do so?
Aggr(Sum([Shipmt Qty])/([Shipmt ETA]>(today())),[Shipmt ETA]>(today()))