how to count rows in a report greater then zero

[c123456rystal]

I have a report and need to count nr. of rows with value greater then zero

 

[lbass]

Insert a running total that does a count of a non-null field, uses a formula for evaluation:

{table.field} <> 0

...and resets never. Place the running total in the report footer.

-LB

 

[c123456rystal]

Thank you so much. It did work.
Now I have to use an average. Each column has a total (sales) and need to devide the nr. of rows to the total sales.

Many thanks

 

[lbass]

You should lay out all your requirements in your initial post. Are you trying to find the average sales for those rows where sales > 0?

-LB

 

[c123456rystal]

I should and I am sorry I did not

I have a report reflecting sales by month in columns.
Rows represent name of customers.
I grouped the report by area.

From above, I inserted a runing total counting the rows. (representing how many customers had sales)
Now, I have to insert a formula were I take the count of rows devided by the total sales for all customers.
Thank you,

FR

 

[lbass]

You didn't answer my question. I will assume you mean you want the average of non-zero sales. You would use a formula like the following in the report footer:

sum({table.sales})/{#countofnonzerosales}

If you want to do this at the area group level, you would need to add another running total like the first except that it resets on change of group: area. Then in the group footer, you would use a formula like this:

sum({table.sales},{table.area})/{#countofnonzerosales-grp}

-LB

 

[c123456rystal]

Thank you so much. It worked.
Now if the total sales is a zero, and total count of rows was zero, when I crate the average I get an error as a division by zer. How can I go around that?
Again, thank you so much.

 

[lbass]

Sorry, I should have built that into the formuals:

if {#countofnonzerosales} <> 0 then
sum({table.sales})/{#countofnonzerosales}

if {#countofnonzerosales-grp} <> 0 then
sum({table.sales},{table.area})/{#countofnonzerosales-grp}

-LB

 

[c123456rystal]

Thanks again.
It worked.
How do I go about to keep the Months Jan/Feb/ etc. to repeat on each page and the Report header?

Thanks

 

[lbass]

What do you mean? The column labels? If so, place them in the page header.

-LB

 

[c123456rystal]

It worked, you are the best!!!

 

[c123456rystal]

My report is not pretty at all. Can I apply some gridlines or a template?

 

[lbass]

I think templates are intended to be applied before report design. However, save your report first and then go to report->template expert and select a template.

-LB

 

[c123456rystal]

Now, I think I need to insert a parameter asking what area?

 

[lbass]

You should start new threads on new topics.

-LB