1 Reply Latest reply on Oct 2, 2013 12:04 PM by Jonathan Drummey

    Calculate averages by both pane and cell within the same calculated field?

    Derek Yuen


      I am working on a viz with heirarchical variables akin to 'Product Name' and 'Product Category' as adjacent rows. The measures are lower and upper confidence values for a given mean value, plotted per product name (ie averages per cell).


      These confidence intervals are then visually compared in the viz to the lower and upper confidence intervals for the Category-level mean value (ie averages per pane). The Category-level confidence intervals are displayed as reference bands so as to allow simultaneous display with the Product-level values, which are plotted as marks.


      I would like to calculate a new value called 'Outlier', which codes a value of say either -1, 0, or 1 for each product. '-1' would mean a below outlier, which is when the Product-level upper CI is smaller than the Category-level lower CI. '1' would mean an above outlier, which is when the Product-level lower CI is greater than the Category-level upper CI. '0' would be non-outliers, ie all other cases.


      This should have been straightforward to code except for the fact that I don't know of any way to reference a table calculation performed in a reference band in my 'Outlier' calculated field. I also don't know how to specify in a calculated field's syntax that I want to compare the average of a measure by CELL against the average of that same measure by PANE. Any help would be much appreciated.