4 Replies Latest reply on Jun 9, 2015 1:36 AM by BorisKamp

    sub summary when sorted by field not working

    BorisKamp

      Title

      sub summary when sorted by field not working

      Post

      Hi guys,

      I have this layout (see attachment), however, the total investment percentages (8% as seen) do not work in my sub summary layout part.

      What I want this field to be is the percentage compared to the total worth of the fund (vermogen, $2.000.000) see the left bottom.

      so $41.820 / $2.000.000 = 2,09% for the first sub summary.

      All the fields seen come from one table called "Properties" the percentage field is as following: 

      Summary Total Investment % = Summary Total Investment / AWF III Vermogen

      Summary Total Investment = Total of PurchaseInfoFinalInvestment (total investment for a property)

      I guess I'll need a TO to achieve this, I tried but did not get it to work.

      What am I doing wrong here?

      Thanks!

       

      Screenshot_2015-05-19_09.15.16.png

        • 1. Re: sub summary when sorted by field not working
          philmodjunk

          "did not get it to work" is one of my least favorite phrases in this forum. Not so much that something didn't work for you, but that you haven't told use exactly HOW it failed and this can be useful clue to those trying to help you.

          But in your case, it looks like you are trying to use a summary field in a calculation and not getting the desired subtotal value to use in that calculation.

          When a calculation field refers to a summary field, the value returned is based on the current found set, not the current sub summary part's sub total. To get that sub total instead of such a "grand total", requires using the GetSummary function.

          If you have a sub summary part with "fieldB" specified as the "sorted by" field, this expression:

          GetSummary ( SummaryField ; FieldB )

          Will return the subtotal that you see in the sub summary part.  Like a sub summary part, the records must include FieldB in the current sort order or no value is returned by the function. Unlike a sub summary part, FieldB must be a field defined in the same table as SummaryFIeld and the calculation field.

          • 2. Re: sub summary when sorted by field not working
            BorisKamp

            Hi Phil,

            sorry for my easy 'did not get it to work' phrase, I should have elaborated more.

            Reading your reply, my ways of how I tried to achieve this we're way off.

            I understand your way of doing this but do not yet get how I can get the percentage.

            Im trying this:

            GetSummary( Summary Total Investment / Total Fund Worth ; PropertyInfoStatus )

            which is wrong because I cannot put a non summary field as the first argument in the GetSummary function.

            What am I missing here? 

            • 3. Re: sub summary when sorted by field not working
              philmodjunk

              Are Total Invenstment and Total fund Worth both summary fields?

              If so you may need two Get summary function calls instead of one unless one of these summary values represents a "grand total" across your entire found set.

              If both are summary fields used to get sub totals:

              GetSummary ( Summary Total Investment ; break field ) / GetSummary ( Total Fund Worth ; break field )

              I didn't specify the break field as I'm not sure that both fields would use the same break field in this case.

              If Total Fund Worth is a number field, then you would use:

              GetSummary ( Summary Total Investment ; PropertyInfoStatus )  / Total Fund Worth

              • 4. Re: sub summary when sorted by field not working
                BorisKamp

                Thanks Phil! got it to work now.

                I used GetSummary ( Summary Total Investment ; PropertyInfoStatus )  / Total Fund Worth because I am using a number field.

                Thanks for your detailed explanations!