8 Replies Latest reply on Mar 8, 2012 11:25 AM by philmodjunk

    Subsummary Report w/Percentages

    EmmettOliver

      Title

      Subsummary Report w/Percentages

      Post

      Hi all,

      How do I get the percentage columns to not display the ? mark when the result is zero?  Also, I would like to get the quarterly percentages to total.  For example the quarterly total for Q1 should be 100 and Q4 should be 67 and the yearly should be 43.  See attachment.

      Thanks, Emmett 

      FiscalQuarterLayoutWithMonths.JPG

        • 1. Re: Subsummary Report w/Percentages
          philmodjunk

          What's the calculation you are using to compute a percentage? On trick is to include an If function that returns 0 of the divisor is empty or zero instead of the percent calculation.

          • 2. Re: Subsummary Report w/Percentages
            EmmettOliver

            Below is a screen shot of the fields for the different month's percentages.  I thought I could create fields similar to the columns for the "C" column and "w/in 24" columns, but the percentages are behaving the same.  I appreciate the help. 

            • 3. Re: Subsummary Report w/Percentages
              philmodjunk

              Taking "c2004PercentageJan" as an example, you could write it this way so that you get a 0 instead of the ?: (If %100 is a better value than 0, use a 1 in place of the zero.)

              let ( Divisor = GetSummary ( sMonthlyClosedTotalJan ; Investigator ) ;
                      If ( Divisor > 0 ; GetSummary ( sMonthlyDistTotalJan ; Investigator ) / Divisor ; 0 )
                    )

              • 4. Re: Subsummary Report w/Percentages
                EmmettOliver

                 Hey Phil,

                Thanks.  I just got it going using the first suggestion you made about the divisor being zero. Although, yours looks much smoother.  Now, I cannot get the percentages to return the correct number on the Trailing Subsummary (The Quartely Totals).  Also, I would like to get the annual total for the department as a whole, but I cannot get that to work either.  I'm stumped.

                Emmett

                • 5. Re: Subsummary Report w/Percentages
                  philmodjunk

                  What do your current attempts to produce these values look like?

                  • 6. Re: Subsummary Report w/Percentages
                    EmmettOliver

                    Well, the ole light bulb finally went off. 

                    Monthly Totals work using: 

                    If ( sNovClosedTotal = 0 ; 0 ; sMonthlyDistTotalNov / sNovClosedTotal ) * 100

                    Quarterly Totals work using: 

                    If (  Sum ( sSepClosedTotal ; sOctClosedTotal ; sNovClosedTotal ) = 0 ; 0 ; Sum ( sMonthlyDistTotalSep; sMonthlyDistTotalOct; sMonthlyDistTotalNov) / Sum ( sSepClosedTotal ; sOctClosedTotal ; sNovClosedTotal ) * 100 )

                    • 7. Re: Subsummary Report w/Percentages
                      EmmettOliver

                       Hi All,

                      With everything remaing the same, i changed the calculation to:

                      If ( sJanClosedTotal = 0; 0 ; GetSummary ( sMonthlyDistTotalJan; Investigator 2::UserName) / GetSummary ( sJanClosedTotal; Investigator 2::UserName ) * 100 )

                      I did this to use account names for tracking purposes.  All other reports are working as expected as well as this report too, except for the percentages again.  Is there some kind of trick I need due to the fact UserName is from a related table?

                      Thanks, Emmett

                       

                      • 8. Re: Subsummary Report w/Percentages
                        philmodjunk

                        Is Investigator 2::UserName part of your sort order?

                        You can specify fields from a related table in your sort order and sorting on Investigator 2::UserName is NOT the same as sorting on Investigator::UserName.

                        Is it a one to one relationship from the current table to Investigator 2? (references to a field from a related table will refer to the "first" such related record--which might not be the one to use for the specified sort order.)