nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Removing outliers in a crosstab

boris256

I have a crosstab with some data for which I calculated the count (# of observations) and the mean. Now I need to find the count and mean excluding outliers, which are defined as values more than two standard deviations away from the mean. In other words, I have columns: # obs, average, and standard deviation. (The aggregation is by hour, so the rows are hourly periods). I need to calculate two new columns: # obs adj and average adj, which will use only the values that fall within the 2-standard deviation range.

This is easy to do in a table by using filters. But I need to use a crosstab for this project, and the filtering in a crosstab works differently: I can only filter by measures in the data cube; I can't use calculated data fields.

Any ideas would be appreciated!

Find more posts tagged with

Comments

kclark

Can you post your rptdesign please?

boris256

Sure, here it is. It's a simplified version showing one crosstab instead of the six in the original, but they all function the same.<br />
<br />

boris256

Update: I looked into scripting the crosstab, and I see that I can write a script modifying various properties, such as this onCreate script:

function onCreateCell( cellInst, reportContext )
{
if (cellInst.getCellID() == 3467){
if (cellInst.getDataValue("Trip Obs 2_Time Periods/SUPP_NO_Columns/Station Name") > 4){
cellInst.getStyle().setColor("Green");
}
}
}

However, since I need to recalculate the aggregation itself, I need access to the underlying data for the aggregation. Is it possible to get it? Maybe I should look at onPrepare instead?

Once I have the data, I can loop through the data items, comparing them with the standard deviation (in the previous column) and calculate the adjusted count and mean without the outliers.

A possible alternative solution is to use the script to adjust the filter of the data item so the aggregation happens automatically but using correctly filtered data. Is that possible?