0 Replies Latest reply: Jun 27, 2012 10:34 PM by jialiang RSS

    Aggregation or set analysis for advanced searches on system date

      Hi,

      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()))

       

      Thanks.