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

# Subsummary Report w/Percentages

### 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

• ###### 1. Re: Subsummary Report w/Percentages

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

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

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

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

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

• ###### 6. Re: Subsummary Report w/Percentages

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

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