8 Replies Latest reply on Mar 23, 2009 10:38 AM by philmodjunk

    Help in creating summary field in a financial budget

    homeriom

      Title

      Help in creating summary field in a financial budget

      Post

      3-19-2009 

      Greetings and thanks for taking my question. 

      Version 9 

      Question: I have created a financial budget using the numbers from the old year budget and projecting a New Year budget. It also includes a field for dollar change and (%) percentage change. This all works fine. I also subtotal the various accounts by a field and I obtain subtotals calculations for these fields. The dollar change field on the subtotal summary is correct.  The percentage increase or decrease for the subtotal is incorrect. I am assuming that I need to make this a summary field instead of a calculation field, but I have not been able to make this come out correctly. My last attempt of creating this summary field was using: average of, summarize information. I trust I am explaining this correctly.  Thanks for your help...hk

        • 1. Re: Help in creating summary field in a financial budget
          philmodjunk
            

          It's hard to tell you what to do without a better understanding of your "percent decrease" calculations. I can point out that you can use summary fields in the definition of a calculation field and treat this field as you would a summary field for placing it on a layout.

           

          Example:  you can define calculation as SummaryFld1 + SummaryFld2

           

          Sometimes this approach will give the summary calculation you need.

          • 2. Re: Help in creating summary field in a financial budget
            homeriom
              

            Thanks for responding.

             

            Each records has a change in its percentage, positive or negative, depeding on the budget increase or decrease. My individual records record the change sucessfully. It is when I group the data that the percentages to not equal the percentage change total of the records grouped.

             

            The calculation formular for each record is:

            Dollar change in budget (new budget substracted from old budget) divided by old budget. This works fine.

             

            This does work when I group the data.

             

            Possibly this will assist. Thanks again...hk

            • 3. Re: Help in creating summary field in a financial budget
              homeriom
                

              daaaaaaaaa

               

              Should be "does not Work" when I group the data.

               

              sorry...hk

              • 4. Re: Help in creating summary field in a financial budget
                philmodjunk
                  

                Here's a forum tip I recently learned: There's a forum option in a drop down menu in the upper right corner of your posted message that let's you edit your post after you post. It's saved me a number of such "daaaaaaa" moments :smileyvery-happy:

                 

                If I undstand correctly, the problem is that you don't get the sub total you need, correct? If so, look up the function "GetSummary" in the on line help function. I think it'll give you what you need. Getsummary enables you to produce calculated subtotals if you sort on the same field you specify in your "break" field (a parameter you specify in the function.)

                 

                Thus you might try: GetSummary(new budget,breakfield) -Getsummary(old budget,breakfield)) / Getsummary(old budget,breakfield)

                 

                 

                GetSummary ( summaryField ; breakField )

                • 5. Re: Help in creating summary field in a financial budget
                  homeriom
                    

                  3-20-09

                   

                  Greetings!

                   

                  Thanks for responding again. Also, thanks for you hint on the edit of messages.

                   

                  I have reviewed the get summary. It is just a little more in depth than I can utilize and/or have the understanding for right now....homer

                  • 6. Re: Help in creating summary field in a financial budget
                    philmodjunk
                      

                    GetSummary should probably be called "GetSubtotal". The break field is the field you sort on in order to organize your report into your sub summary groups. Make sense?

                     

                    Let's say you have three fields DateField, Cost and TotalCost--a summary of Cost.

                     

                    If you sort your records by DateField with a subsummary part (when sorted by DateField) you would see:

                     

                     -Subsummary Part

                       DateField:3/1/09,   Total Cost 70.00

                     -Body Part

                        cost: 20.00

                        cost: 50.00

                    -Subsummary part

                       dateField 3/2/09,  Total Cost 30.00

                     -Body Part

                        cost:  15.00

                        cost:  15.00

                     

                    Thus: GetSummary(datefield,TotalCost) will return 70.00 on the first two records and 30.00 on the last two records, but only if you've first sorted by date.

                     

                     

                    Hope that helps.

                    • 7. Re: Help in creating summary field in a financial budget
                      obeechi
                        

                      You know, don't why, but I get confused between sum(), summary, getsummary()

                       

                      Like, with just a one table database, and no relationships (only one table occurrence), if you create a subsummary report layout, sort by the breakfield, and go to Preview, if the Summary field is in the sub-summary part, and the GetSummary field (based on the same summary field) is also in the Subsummary part, you get the same number for both. So what is the difference between them? 

                      • 8. Re: Help in creating summary field in a financial budget
                        philmodjunk
                          

                        That last post is encouraging. You are starting to figure things out.

                         

                        GetSummary and Summary will give you identical values if both are placed on a subsummary part that sorts by GetSummary's break field. But notice that if you don't define the subsummary part, (but do sort on the break field), GetSummary will still return the value you now see, while the Summary field might show an entirely different total. Thus, you can use GetSummary in situations where the current layout isn't compatible with the subtotal that you need in a script or calculation definition.

                         

                         

                        Sum, on the other hand, has a variety of different uses that are spelled with good examples in your on line help file.

                         

                        Sum(field1, field 2, field3) is the same as field1 + field2 + field3

                         

                        Sum(repeatingfield) adds up all the repetitions in the repeating field

                         

                        Sum(relatedtablename::field) adds up all the values of field from all the records in RelatedTable that are related to your current record--this last option is great when you list values in a portal and want to show a total for all the records listed in the portal.