how to calculate percent of total in a report?

[leeroi]

I have a report which contains a column of numbers.
I would like to create a variable for a report that computes the percentage of these numbers that meet a certain criteria. For example, if my column has the numbers 1,2,3,4, and I want to know the percentage of the total number of entries which have a value of less than 4, then the answer would be 75%.

Lee

 

[tledwards]

Create a entry in the query for the report or a hidden text box in the detailed section on the form with the following as Data source;

IIf(mycolumn < 4, mycolumn,0).

Then create a text box in the footer to sum these values and calculate your %.

 

[leeroi]

This is not working quite right. I created a new text box in the detail section of the report, entered the code in the data control source box, named the textbox &quot;DaysOK&quot;.

e.g.: IIf (daysPassed<4, daysPassed,0).

When I ran the code the result for each record was the same value as in the original column. Thus the numbers in the original column &quot;daysPassed&quot; and in the new column &quot;DaysOK&quot;, are identical. I'm assuming this is the desire result for this step?

Next I created a textbox in the footer of the report. I really wasn't sure how to go about writing the expression, but I came up with &quot; count(daysOK)/count(DaysPassed), thinking this give me the percentage I was looking for.

Didn't work.. and I'm not sure what I'm doing wrong.

Lee>

 

[tledwards]

I misunder that you want the % of those less than 4. The formula should be changed to =IIf(DaysPassed<4,1,0). This should give you a 1 for item you want.
Then in the footer some times a calculation added to the form will not work in combination so first you have to creat a text box for the calculation. Then use the text box name in the calculation. So in the footer set up a text box, i.e. sumDaysOk, with the control source &quot;=sum(dayOK)&quot;. Then change your &quot;count(daysOK)/count(DaysPassed)&quot; to &quot;=sumDaysOK/Count(DaysPassed)&quot;

 

[leeroi]

Thanks for the clarification, but it's still not working quite right. I updated the formula IIf(dayspassed<4,1,0) and named the textbox DaysOK. The result is a value of 1 if the original value for DaysPassed was less than 4.

If I understood you correctly, there are two additional text boxes to set up- one to sum the &quot;ones&quot; in the first formula and a second to compute my percentage.

The second text box was named SumDaysOK, with a control source as =sum([DaysOK]). This was placed in the footer section. When I run the report, I'm asked to enter a parameter value for DaysOK- i.e., it doesn't recognize DaysOK.

Lee

 

[tledwards]

You are not going crazy. I am. I thought that technique had worked before but it didn't work for me just now but this did. Make the control source for the SumDaysOK
&quot;=Sum(IIf(dayspassed<4,1,0))&quot;. You will not need the DaysOk text box. Or better yet just us this in you % calcualtion:
&quot;=Sum(IIf(dayspassed<4,1,0))/count(DaysPassed)&quot;.

 

[leeroi]

By Jove, that did it!

Thanks for sticking with me!

Lee

 

[leeroi]

I need to modify a similar calculation to the above so that it only includes records in the calculation which meet a certain condition. Here is the formula as it now stands: =Sum(IIf([DaysRptToFamily]<2,1,0))/Count([DaysRptToFamily]). However, I only want to include records in the computation if another variable associated with each record [rptMailedToParent] has a value of 1.

How can I include this condition in the original formula to filter out records not meeting this criteria?

Please note that this formula is entered as the control source in a report text box.

Thanks,

Lee