11 Replies Latest reply on May 15, 2012 12:19 PM by philmodjunk

    database calculations

    docgould

      Title

      database calculations

      Post

      I am composing a database to track my revenue each month and quarter.  I have created a very simple form, with three data fields:

      date billed out - date field

      date collected - date field

      amount collected - currency field

       

      The number I enter is a cummulative number. It represents the amount collected (as of the 'date collected') for the month billed out. ie: 

      date billed - Oct 2011

      date collected - Feb 2012

      amount - $52423.46

      This means that of all the money we billed out in Oct 2011, $52k had been collected as of Feb 2012

       

      Here is what I'd like to be able to calculate:

      cummulative collection for month collected - I know that it needs to be able to identify the correct records (the month in question), then grab the correct data field (amount) and then add it up. 

      net collection for month - this is simply the cummulative amount from that month, minus the cummulative amount from the month before

      quartly total - this would be the net total from a three month period

       

      And obviously I am going to try to format this information into a report that is easily readable. I am nowhere versed enough to write this formula. I would appreciate any help here. I haven't been able to play with all of the starter solutions yet but does anyone know if there is a ready solution out there? If not, I am willing to do this from stratch with a little direction.

      I am willing to share the data I have already entered if it would help. There are no identifying data in it so it's ok.

       

      Thanks,

      Jason

        • 1. Re: database calculations
          philmodjunk

          I'm not sure I understand your setup.

          If you have a record with "date billed"  Oct 2011 and date collected Feb 2012, do you also have a record with Date billed = Oct 2011 and date collected Jan 2012? Once such record each month? If so, does the amount for the Feb 2012 record include the amount shown in the Jan 2012 record? (Just the difference tells you how much was collected for the month of February.)

          Where does this data come from? Could you access the original transactions or must we work from these records? (It may be simpler to work directoy from the transactions that are totalled in these records.)

          • 2. Re: database calculations
            docgould

            You are correct. Here is an example:

            record 1

            date billed: 10/11

            date collected: 2/12

            amount: $10

            record 2

            date billed: 10/11

            date collected: 3/12

            amount: $30

             

            The $30 from record 2 includes the $10 from record 1. Unfortunately I do not have access to the data that gives me this amount. If it would make it easier, I have a excel spreadsheet I have made with this data, calculating the totals I am looking for. I have uploaded a screenshot of it. Hopefully this will clear up what I am trying to explain ...

            • 3. Re: database calculations
              philmodjunk

              From a business practices point of view, I find myself wondering where you'd stop adding columns to that spread sheet. Can't imagine you'll still be tracking collected funds for a september 2011 month three years from now....

              You can compute the totals you want. How you do it depends on you you need to organize these records in your report.

              First, is the month/year imported into the database as text? It will make life easier if we can convert it into an actual date value and we can devise a calculation that returns a date for the first day of that month and year.

              If you sorted your records in a found set where you sorted first by month billed, then by collection month, you can use getNthRecord to access the amount field of the preceding record in the found set to subtract it from the current collection month's amount:

              Amount - If ( get ( RecordNumber ) = 1 ; 0 ; GetNthRecord ( Amount ; get ( RecordNumber ) - 1 )

              If you need this data when you don't have your records sorted in that fashion, you'd need to set up a self join relationship where a calculation field computes a date for the preceding months in order to link to the record for the preceding month. Then your calculation can refer to an amount from that related table occurrence.

              • 4. Re: database calculations
                docgould

                This is a excel spreadsheet I set up to help me organize myself as I set up the database.

                 

                I have followed your instructions so far:

                1. I sorted my records by date billed.

                2. I created a calculation field using the formula you laid out for me (thanks!). This seems to work but needs a little tweeking. The problem I am having is that it isn't treating a new month billed as a new starting point. For example, the first amount collected for October (in Oct 2011) is being subtracted from the last amount collected for September (Mar 2012). I haven't created any calculations that manipulate the date yet. Is that the next step?

                • 5. Re: database calculations
                  philmodjunk

                  We can make that If function a bit more sophisticated:

                  Amount - If ( GetNthRecord ( Month Billed; get ( RecordNumber ) - 1 ) = Month Billed ; GetNthRecord ( Amount ; get ( RecordNumber ) - 1 ) )

                  • 6. Re: database calculations
                    docgould

                    Aweseome. This is going very well and teaching me a lot (or, at least, I think I understand what's going on).

                    From here, I was able to generate a report that grouped the records into month collected and then calculated net collection subtotals. At the end I was able to get the net collection grand total.

                    Is there an easy way to further group these numbers into quaters? I know I can do a "Find" for the three months I am looking for and then generate a report that way.  But is there a way to create another level of grouping, and subtotals? Ideally, I would like to see each months net collections, each months total net collections, each quarters net collections and then also the grand total for the year. 

                     

                    Thanks.

                    • 7. Re: database calculations
                      philmodjunk

                      Have you ever created a summary report with sub summary layout parts? That's what you can use to get quarterly sub totals here.

                      I don't know if your "date" fields here are text or a date field formatted to hide the day. Either works and an actual date field makes for simpler sorting as sorting by such a field will put the records in chronological order for you.

                      Either way, you can constuct a calculation field that returns a number for the year and another calculation that returns a number for the Quarter.

                      You can then sort your records first, by the year, quarter and month fields in that order.

                      Then you can add a sub summary layout part "when sorted by" your year field. You can put the Year field plus any layout text you want as a sub heading if you use the print above option. Use print below and add a summary field to it and you can display a yearly total.

                      You can then add sub summary parts when sorted by your quarter field to get sub headings and sub totals for each quarter.

                      Keep in mind that thes sub summary parts will not appear unless your records are sorted by the same fields specified for the sub summary parts.

                      • 8. Re: database calculations
                        docgould

                        I have run into snag I can't quite explain (or fix). I had this database working well last month. I had entered all the info from Sep 2011 until Mar 2012 and it was able to calculate my net and gross collections.

                        Today, I received my collections for April. I entered the data but the database did not calculate the net collection for that period. I tried entering month collected first, month billed first. I tried having everything sorted by dated billed, date collected. Nothing changed it. I erased all of the April ones and started over with no change. I tried starting with April collected, Sep billed and I tried starting with April collected, April billed. I also deleted the values for past ones and re-entered them - these net collections were correctly calculated. 

                        I have included a screen shot with Apr collected, Sep billed still there. You can see that it did not subtract the amount from Mar collected, Sep billed. Here is the calculation I have in the net collection field:

                        amount - If ( GetNthRecord ( month billed; Get ( RecordNumber ) - 1 ) = month billed ; GetNthRecord ( amount ; Get ( RecordNumber ) - 1 ) )

                        Can it somehow be connected to the fact that all the old data was input at the same session and this is a different session? That is the only thing I can think of right now.

                        Thanks - Jason

                        • 9. Re: database calculations
                          philmodjunk

                          I'd double check the value in the Month Billed field. Is this a text field? (I forget). If it is, it may have a space or other invisible character so that it does not match the value in Month Billed in the previous record.

                          • 10. Re: database calculations
                            docgould

                            There were calendar fields but in edit box mode. I switched both month billed and month collected to drop down calendars and it seemed to work. It's a touch more work, having to scroll back, but hopefully it'll work next month without a problem.

                            • 11. Re: database calculations
                              philmodjunk

                              These could be date fields with a identical dates entered or text fields with identical text entered. The key is to have identical data in the field for every record for the same "Month billed".