1 Reply Latest reply on Jul 22, 2014 12:29 PM by philmodjunk

    Make a monthly report which includes previous month results

    PedroTorres

      Title

      Make a monthly report which includes previous month results

      Post

           Hi,

           my company rent containers to customers. Each month we have to do a balance of containers (returned or rented) for each customer. I have a customer table and another table for containers.

           I would like to display a monthly report which include the previous month balance at the top of the layout.

           Example for the month of july:

           balance from june: returned 5 containers, rented 4 containers, so balance is 4-5 = -1.

           then

      01/07/14, 1 returned, 0 rented

      02/07/14, 0 returned, 0 rented

           etc.

      31/07/14, 0 returned, 4 rented

           balance is 4-1 = 3

      do the total balance at the bottom: -1 (june) + 3 (july) = 2

           The difficulty is to include the balance of the previous month in the calculation of the current month.

           I hope somebody can help,

           PT

        • 1. Re: Make a monthly report which includes previous month results
          philmodjunk

               You can use a relationship that refers to all related records with a date one month less than either the current record's date or the current calendar date from your computer's system clock.

               The trick is to use a calculation that returns the same value for all records of the same month and year.

               DateField - Day ( DateField ) + 1

               Will return the date for the first day of the month for all dates in that month.

               Date ( Month ( DateField ) - 1 ; 1 ; Year ( DateField ) )

               Will return a matching date for the previous month.

               Substitute Get ( CurrentDate ) for DateField in the last calculation to get a matching value for the month preceding the current month as shown on your system clock.