Manual Crosstab needs Median Calculating 1

[DuncanSutcliffe]

I have built a manual cross-tab and have a problem with certain summary values.

The logic is:

Formula 1: If Day = 1 Then [Value] Else 0 (this gives me the total value for day one)
Formula 2: If Day = 1 Then 1 Else 0 (sum this to get the count of values for day one)

To get a mean, I can just do Sum(Formula1)/Sum(Formula 2).

I now need a median, but it's difficult for the following reason. If there are one hundred rows in the source, but only twenty five of them belong to day one, I will have a one hundred results for Formula 1...but seventy five of them will be zero. So the median will always be zero. Even if ninety nine rows belong to day one, there will still be a single zero which will skew the result very slightly.

I somehow need to find out how many values really belong to day one, ignoring all zeros that have been generated by the formula - any ideas...?

Oh - I then need to calculate Std Dev as well.


TIA,

Duncan

 

[midearth]

You don't have any details concerning version, etc. but you should be able to do the following:

if Day = 1 then Median({your.field})

Also for stddev: if Day = 1 then stddev({your.field})

If you have a certain condition to meet then it would be:

if Day = 1 then Median({your.field},{condition.field})

For the median, you may have to sort the records in ascending order.

 

[midearth]

Also forgot: both are print time functions, so you may have to add a first line to return the correct data:

whileprintingrecords;
if Day = 1 then ...

 

[DuncanSutcliffe]

Thanks, but...

Unfortunately, that will show the median of each detail row, rather than of the set that belongs to that day, in the same way that showing the value on condition shows that detail value. But with values you get useable information for each row and you can sum them to get a total, whereas a median is only applicable to a series - i.e. you can have lots of values but only one median for a list.

I don't think this is really a version-specific problem, but I'm using CR 10 Developer edition.

Duncan

 

[midearth]

That's one thing in the details I forgot to ask - is your data grouped in any form? Grouping by day would resolve the issue allowing you to use those formulas but by reading your post that's probably not the case.

 

[lbass]

You might try using a running total where you select your formula, median as the summary, evaluate based on a formula:

{@yourformula} <> 0

Reset on change of group. This would have to be displayed in the group footer. For a median at the report level, create another running total with reset = never.

-LB

 

[DuncanSutcliffe]

Thanks, lbass. That works. As you say it has to go in a footer (damn, there go my carefully crafted drill-downs!) but it is a great solution.

Duncan