Average of non-zero values in a cross-tab

[AndrewBaines]

First the report has to be a cross-tab as there are a variable number of columns and rows. Tried to persuade the customer to set a limit to the columns, but no joy.

Have a cross tab with the grand total of some rows being summed, others averaged - using display string to display the final figure to allow for the averages.

Customer now wants some of the averages to be the average of only the non-zero values.

Anyone got any good ideas?

Thanks

Andrew Baines

http://www.pursuittechnology.co.uk

 

[PatriciaObreja]

I've just created a cross-tab and I notice that AVG function takes only the non-zero values.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[AndrewBaines]

Yes, but unfortunately, I don't need the normal average - I need the average of the displayed values. So one cell may be the sum of several numbers, I don't want the average of all the numbers, just the average of the cell sum.

I've tried using variables checking for non-zero and resetting on each column, but the cross tab appears to evaluate columns first, not rows.

I could do it with a UFL, but customer won't have them.

Andrew Baines

http://www.pursuittechnology.co.uk

 

[lbass]

We would have to know exactly how the crosstab is set up (rows/columns, summaries (type)), and also see how you have set up the current variables within the crosstab (the content of the formulas and where they are placed). Can't tell right now whether you are averaging rows or columns, for one thing.

-LB

 

[AndrewBaines]

It's the rows. Thought I'd be able to do it using running total like formulae. Unfortunately, the way Crystal evaluates crosstabs is very odd - page at a time, columns first. I had a running total to calculate a unique reference for each cell. Oddly, it jumps one between 1st and 2nd column, but not the others.

Think I've worked out a way now - I'll be putting a subreport in the report header with a shared array holding the values I need.

Andrew Baines

http://www.pursuittechnology.co.uk