Manual median calculation ignoring zero's 2

[Bradford847]

Is there a way (I was thinking arrays possibly) that I can manually calculate the median for a group so that it can be used in the conditional highlighter?

I am trying to add conditional highlighting (traffic lights basically) to some values based on how far away they are, in percentage terms, from a median. The median needs to be calculated whilst ignoring zero values.

The out put should look something like

GH1 Customer Value 1 Value 2

Detail A 0 10*
Detail B 2* 12
Detail C 4 0
Detail D 6* 14*

GF 1 Median 4 12

If the threshold was +- 5% then the asterixed numbers should have a different background.

The report groups customers together and displays several different values. I use a running total field to calculate the median for each type of value, with a formula to exclude the zero’s, and display it in the group footer. This is fine. The problem comes when I try to use the median to conditionally change the formatting of the details. The median in the footer cannot be used to calculate the variance from the median, and if I try to use the median in the detail then the median is changed with every detail row.

Any help very gratefully received as I have started to hurt my head by banging it against the wall so often!

 

[IanWaterman]

Running Totals and Vars only evaluate after the details have been displayed.

An option is to build a subreport which executes in each Group header and calculates the Meadian values first.
Then using a shared number var you can use that in your main report to control your highlights.

Might be a bit slow to execute though if you have a lot of Customers.

Ian

 

[Bradford847]

Thanks Ian. I'll give that a try as we don't have that many customers in any particular country so may not be too slow. It will probably be easier than using an array!

 

[IanWaterman]

An Array will not work as you will have to calculate median when Array is populated in group footer.

Good luck.

Ian

 

[lbass]

You could create formulas like this:

//{@Val1}:
if {table.value1} = 0 then
tonumber ({@null}) else
{table.value1}

...where {@null} is a new formula that you open and save without entering anything. Then you can use a formula like this in format field->border->color->background->x+2:

if abs((median({@val1},{table.groupfield})- currentfieldvalue) % median({@val1},{table.groupfield})) >= 5 then crYellow else crNoColor

-LB

 

[Bradford847]

Thanks LB. That's very neat logic.

I've just tried it and I get an error message in the first formula, {@Val1}. It doesn't like using the TONUMBER command to convert the empty formula, which it thinks is a string, to a blank.

Can you think of any way around this?

I'm using CR 2008 by the way.

Many Thanks,

Ian

 

[lbass]

Is your field NOT a number? When you created {@null} you didn't enter anything in the formula area, correct? You should not have entered a space.

-LB