6 Replies Latest reply on May 14, 2012 2:01 PM by philmodjunk

    Monthly report for a multi-month expense.

    OlivierRamoudt

      Title

      Monthly report for a multi-month expense.

      Post

      Hello,

      I'm stuck trying to create a monthly expense report because some of my expenses are asymmetrically spread over several months. For example I get an invoice for a one off insurance policy running from April 2nd till June 6th. How can I get my monthly expense report to report that in April I have 28/30ths of my total amount as expense in May a full months, and in June only 6/30ths. 

      In my cost expenditure table I have two fields to enter the date range: 'cost period from' and 'cost period until'.

      Also using my two date range fields here above I'm having trouble in Find mode to find my expenses in a certain date range cause if I enter dates in both search fields I need to match the exact from and until date. I would like to be able to search say 4/15 until 4/30 and get all running expenses within that range, and also have the cost amount break down to the date range in question. For example here I'd like to be able to enter in Find mode search 'from 04/15' 'until 04/30' (a 15-day period) and if say my rent is running from 03/17-05/16 get 15 days worth of rent back on my report. 

      Any help is much appreciated.

        • 1. Re: Monthly report for a multi-month expense.
          philmodjunk

          Those are definitely challenging issues--which is why I've delayed viewing this issue until I had some time to read it and respond...

          For issue number 1, please provide a more detailed description of your current tables. I can tell that you have a table for logging each expense. Is that the only table or do you have other related tables that are part of this system? I'm thinking in terms of a report table where you have one record for each month to use to link to expenses where the month intersects the date range recorded with your expense.

          For issue #2. (just the find part of it)

          You have two date ranges and such ranges will intersect (if they do) in three different ways.

          Date range 1 can enclose date range 2.

          5/1/2012...5/31/2012 encloses the range 5/4/2012...5/7/2012

          The beginning of date range 1 can fall within date range 2.

          5/1/2012...5/31/2012 intersects 4/20/2012...5/5/2012

          The end of date range 1 can fall within date range 2.

          5/1/2012...5/31/2012 instersects 5/28/2012...6/3/2012

          Heres a method that will pull up records for all three scenarios. It can be done manually or in a script if you use a pair of gobal fields for the search date range....

          enter find mode

          Specify >= Date1 in date start field
          Specify <= Date2 in date end field

          perform the find to find all records where their date range is enclosed by the specified date range

          Return to find mode

          Specify <= Date 1 in date start
          Specify >= date 1 in date end

          Use extend found set to find all records where Date 1 intersects the records' date range and add them to the found set from the first find

          Return to find mode

          Specify <= Date 2 in date start
          Specify >= date 2 in date end

          Use extend found set to find all records where Date 2 intersects the records' date range and add them to the found set from the first two finds.

          • 2. Re: Monthly report for a multi-month expense.
            philmodjunk

            Still thinking about the proportional costing issues and came up with a few more ideas...

            The calculation cost period until - cost period from

            will compute the number of days for your entire cost period.

            Now let's say you have two global date fields, gDate1 and gDate2 that record the current time period of interest.

            Let ( Period = Cost period until - cost period from ;
                   CostAmount * ( min ( gDate2 ; cost period until ) - max ( gDate1 ; Cost Period From ) ) / Period )
                  )

            Should return the fraction of CostAmount relevant to the time intereval gDate1...gDate2.

            Thus you can enter a date range in the two global date fields, run a script to perform the find, and a calculation field with the above expression will then compute the fractional cost amount.

            Please note that I have not had time to test this calculation.

            • 3. Re: Monthly report for a multi-month expense.

              $x = cost per day = $amount / days of coverage

              $datePeriodcost = number of days * $ x

              That was easy...  :)

              Wait, consolidate:

              $datePeriodCost = number of days * ($amount / days of coverage)

              Consultant for hire, Here!  :)

              • 4. Re: Monthly report for a multi-month expense.
                philmodjunk

                Yes Jack, but how do you calculate "number of days"? Wink

                It's getting that number from a pair of overlapping date ranges that's the tricky part.

                • 5. Re: Monthly report for a multi-month expense.

                  First you'd need some place to store the result so lets assume a table "A"

                  Next a start and stop date, the date range entries

                  Start = Your Value

                  End = Your value

                  Days() = the function for getting the day number out of a date, can't remember it right now

                  Note that a +1 or -1 may need to be inserted here or there for accuracy

                  DaysMonth1 = Days(Date(Month(start) + 1; 1 ; Year(start) ) - 1) - days(start)

                  DaysLastMonth= days(end)  the function that pulls the day out of the date

                  In between months days(thatmonth'slastdate)

                  You can work out the looping, not enough coffee yet.

                  I think date(month ; 0 ; year) produces the last day of the month... which simplifies my daysmonth1 calc

                  • 6. Re: Monthly report for a multi-month expense.
                    philmodjunk

                    Or perhaps you'd use the calculation that I've already worked out and posted prior to yours? Wink