4 Replies Latest reply on Feb 8, 2013 11:58 AM by schamblee

    How to calculat today's Box + last day of saling(Box)? on a chain.

    HelderSantos_1

      Title

      How to calculat today's Box + last day of saling(Box)? on a chain.

      Post

      hello good day
           on my  Sales system, i have Table and fields( date; total_sales; total_expenses; remain; box_day
      )

      eg:
                date        total_sales   total_expenses     remain_by_day    box_day
           03/01/2013     $50                   $18                        $32                   $99
           02/01/2013     $45                   $12                        $33                  
      $67
           01/01/2013     $49                   $15                        $34                   xxx

      I intend to calculate the box_day of day 2/1/2013 this way: (34+45) -12  = "67"
           how can make it works?

        • 1. Re: How to calculat today's Box + last day of saling(Box)? on a chain.
          philmodjunk

               I don't understand Your calculation. I can guess that 45 comes from total sales for the month, but where/how do you get 45 34 to add to it?

               And how do you get $99 for 3/1/2013 ?

          • 2. Re: How to calculat today's Box + last day of saling(Box)? on a chain.
            HelderSantos_1

                 34 comes from total_sales - total_expenses on date "01/01/2013". from this point i start to add box_day(from last date"2/1/13") + total_sales(from currentdate"3/1/13") and so on. 

                 49 - 15 = 34 (i just used it to iniciet my system)
                 from there i take (34+45) - 12 = 67 then  (67+50) - 18 = 99

                  

                 date        total_sales   total_expenses     remain_by_day    box_day
                 03/01/2013     $50                   $18                        $32                   $99
                 02/01/2013     $45                   $12                        $33                  
            $67
                 01/01/2013     $49                   $15                        $34                   xxx

            • 3. Re: How to calculat today's Box + last day of saling(Box)? on a chain.
              philmodjunk

                   By any chance, would it be acceptable to reverse the sort order?

                  date        total_sales   total_expenses     remain_by_day    box_day
                   01/01/2013     $49                   $15                        $34                   $34
              02/01/2013     $45                   $12                        $33                  $67
              03/01/2013     $50                   $18                        $32                   $99

              I'm asking that because with the order reversed, box_day can be a summary field that computes a running total of remain_by_day

              With the order reversed, you'd need to use the getNthRecord function to access the Get ( RecordNumber ) + 1 record's remain_by_day field in order to add the two values--which is a more complicated solution.
                    

              • 4. Re: How to calculat today's Box + last day of saling(Box)? on a chain.
                schamblee

                     As long as the records are sorted by date and depending on sort order.

                     if  remain_by_day is the prior record

                total_sales+GetNthRecord(remain_by_day;Get(RecordNumber)-1)-total_expenses

                     if remain_by_day is the next record

                total_sales+GetNthRecord(remain_by_day;Get(RecordNumber)+1)-total_expenses