0 Replies Latest reply on Nov 21, 2013 7:16 PM by Sam Lavers

    Show only the values related to the first date in a date range. Stock movement model

    Sam Lavers

      I have a stock movement report where I want to show opening stock on hand, movements & closing SOH in different colums (by cost centre and item). Stock counts are submitted on a adhoc basis on random dates and I need to allow users to define a date range.

       

      So far I can select the correct data for a date range and sum the movement items but I dont want to sum all the opening and closing stock values. Instead I need to find the first SOH value for each cost centre/item within the date range selected.  (also another colum for colsing stck being the last date in the date range' value)

       

      I'm thinking its a calulated field along the lines of: IF(MIN(DATE)=DATE,[OPENING STOCK ON HAND],0) but i cant mix aggregate and non aggregate.

       

      Any help would be great!!