7 Replies Latest reply on Sep 14, 2010 4:42 PM by philmodjunk

    Change over prior week calculation field?

    Derrenger

      Title

      Change over prior week calculation field?

      Post

      I've built a working database in FMP 11, and designed a summary report that calculates the original and final numbers for each given Shot Rating (in this case A-F). It also calculates the variance between the original and the final budget. I'd like to produce this report on a weekly basis, but it would be really helpful if I could also have it calculate the Change of Prior Week in two separate fields (columns).

      For example, if on week ending 09/11/10 the report said that for 'A' Rated Shots there were 4 Final Shots and the budget was $812,056 (see attached jpg below), and then on week ending 09/18/10 the report said there were 6 'A' rated shots and the final budget was $912,056, I'd like like to add two separate fields: one that calculated the Change Over Prior Week in terms of Final Shot number, and one that calculated the Change Over Prior Week in terms of Final Budget number... and in this example the first field would read 2, and the second field would read $100,000.

      Since FMP is always current, I don't know how to take a snap shot of a report and make a comparison at a later date. I come from an Excel background, and back then I would simply "save a copy as" for each week ending, and then I set up a formula that subtracted one week ending report from another.

      Thanks for your time and attention in this matter, and I look forward to hearing your input.

      costs.jpg

        • 1. Re: Change over prior week calculation field?
          philmodjunk

          A lot depends on how you've structured your tables and related them. A summary report can certainly be constructed that groups records by common value (such as the week and/or shot rating) so that subtotals can be computed and displayed. A relationship can be used to access an aggregrate value of selected records from the previous week as well.

          But all this depends on the structure of your tables.

          • 2. Re: Change over prior week calculation field?
            Derrenger

            Thanks Phil,

            I do realize that this was a pretty broad request, and surely the database/table design would make all the difference. I know I could get a summary based on week ending expenses if every cost was being coded on a weekly basis. Unfortunately this summary report is completely based on projections, and the totals you see above are derived from approx. 15 different fields each. I didn't associate each field with a weekend date because they are not actuals and they fluxuate as the project wows and flutters.

            Right now the reports only gives me a current snapshot, and I was hoping to figure out a way to lock those numbers in, and compare them to another given time. May be something I have to do with Excel.

            cheers,

            -DD

            • 3. Re: Change over prior week calculation field?
              philmodjunk

              Unfortunately this summary report is completely based on projections, and the totals you see above are derived from approx. 15 different fields each.

              I don't see why that creates a problem for the summary report.

              What you want should be possible. I just can't give you any specific recommendations without a better idea as to how you've structured your database.

              • 4. Re: Change over prior week calculation field?
                Derrenger

                You'd probably need more information than I can provide here... but I can tell you that all of my Shot Count # fields and Shot Cost $ fields, as well as my Rating field, are in the same table (thus no relationship). I'm not sure if this is a major no-no.

                I have updated the image above to give you a snapshot of the financial portion of my layout.

                I have one record for every shot (I have 500+ shots), and these two fields are referenced in My Summary Report when sorted by Rating.

                Basically, I enter my information into the fields under the Raw Shot Info column,

                Count # = Original # + Added # + C/O #

                Cost $ = Original $ + Added $ + C/O $

                Let me know if this makes sense, and if you have any better design recommendations for my next project.

                Thanks,

                -DD

                • 5. Re: Change over prior week calculation field?
                  philmodjunk

                  That is extremely minimal information to go on. It looks like you have either 10 fields or one field with 10 repetitions for recording cost data (Don't see how you use them just from your screen shot.)

                  Generally speaking, that's an approach that greatly complicates the function of your database when it comes time to set up reports. A related table of cost records displayed in a portal would likely simplify things for you and be much more flexible as well.

                  What tables have you defined?

                  How are they related?

                  • 6. Re: Change over prior week calculation field?
                    Derrenger

                    I realize that it's very minimal, and just didn't want to complicate the issue by showing extra fields and data that weren't applicable.

                    Currently all of these fields live in one table, probably about the only thing I did right was give each field a different name so there is no repetition, but I think I see what you're saying about using a portal.

                    Creating separate tables for Shot Count and Shot Cost and a new record in those tables when a count/cost changes. By adding a date to that change would allow me to narrow down the data on the report based on a given range of dates, and allow me to calculate a variance based on those findings.

                    I could then create several portals that only show the most current information on the page I am now using to enter information (instead of overwriting my original data).

                    If I am on the right track, wish me luck, and if I am way off... any logical suggestions you might have for a novice like me would be greatly appreciated.

                    cheers,

                    -DD

                    • 7. Re: Change over prior week calculation field?
                      philmodjunk

                      In addition to portals, you can simply use Finds to pull up subsets of the total records. The results can be displayed in a list view, table view or the summary report I've mentioned previously.