5 Replies Latest reply: Jul 10, 2013 2:00 AM by Kalyana Sundaram RSS

    Average Stock

    Kalyana Sundaram

      Hi all,

       

       

      I have developed Inventory application, and i brought closing stock value as of now date.

      But I need to bring the average stock value, by the following method

       

      Closing Stock(whole stock value for a month)/No. of Days

       

      my closing stock expression

      sum({<Date={"<=$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Purchase}>} Stock)

      +

      (sum({<Date={"<=$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Sales}>} Stock))

      +

      sum({<Date={"<=$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Damage}>} Stock)

      +

      sum({<Date={"<=$(vDate)"}, FinYear=, FinMonth=, Date=, EntryType={Return}>} Stock)

       

      ---vDate = max(Date)

       

      for the avg stock, my expression is as

       

      above cl.stock expression / count(distinct Date)

       

      but the avg values arrived are wrong, b'coz, in my expr. i used max date, so if i select any year, month...

      stock value shows the respective monthend date cl.stock....

       

      so the avg calculates by taking the cl.stock value and divide by no. of days selected, but it should not be like this....

       

      avg should be arriving by this type

      calculate the stock sum of whole selected month and divide it by no. of days

       

      can anyone please help me..

       

      its very urgent...

       

      Thanks in Advance to everyone who ever reply to me...

       

      Thanks,

      Kalyan

        • Re: Average Stock
          Stefan Wühl

          Kalyan,

           

          you shouldn't use Date field selection multiple times in your set modifier, since you set Date according your variable, remove the 'Date=, ' part in your set expressions.

           

          If you need to know the day number of your max date within the month to calculate your average, you can also just use

           

          =day(vDate)

          • Re: Average Stock
            Lucian Cotea

            If you want AVERAGE_STOCK = STOCK_AT_THE_END_OF_EACH_DAY / NO_OF_DAYS then you have two choises:

             

            1. Create a separate time line (independent) and use it to calculate stock at the end of each day (heavy-duty calculation in interface when used, suited for a large number of transactions)

            2. Calculate in script all the stocks at the end of each day in a separate table and read the values from it (suited for a smaller number of transactions)

            • Re: Average Stock
              Kalyana Sundaram

              hi all,

               

              i had calculated the average by using the following expression

              rangesum(above(total Stock, 0, rowno(Total)))/count(distinct date)

               

              the expression produce the average correctly, only when we use date field as a dimension.

              If i remove date from dimension, the average calculates as i had told in the original discussion

               

              i.e. average takes max.date stock and divide the no. of days selected

               

              so again am stucked for a long while.

               

              Please help me to solve the issue.

               

              Thanks in Advance,

              Kalyan