Dynamic Subtotals

stefan · April 19, 2010 10:17AM

I have a simple table
Product 1, Sales = 4, FTE = 2, [calculated field] Sales/FTE = 2
Product 2, Sales = 6, FTE = 4, [calculated field] Sales/FTE = 1.5

When I select either item in the a bar view with Sales/FTE as my measure I want this value (either 2 or 1.5) to be displayed as Sales/FTE.

However, when I have both items selected, for Sales/FTE I dont want to sum, or take median, or mode etc What I want to have is 1.666 =10/6 = (4+6)/(2+4).

So I guess I want to use a subset_sum(Sales)/subset_sum(FTE) where the subset is the current filtered data. In my real data set, I have too many filters to define all subsets manually - I just want to use the current filter as selected from the devices.

Or is there an easier way that I have missed?

How should this be done?

Regards,

Stefan

stefan · April 19, 2010 10:34AM

Or can I specify a query (e.g. IN, OUT, ALL) in the subset function.

There must be a way surely...?!

April 23, 2010 11:30AM

Stefan

The way to get the result you want is to use Aggregation, and the [Formula Result] aggregation approach. It's a little tricky, and for the sample data set you have provided we will need to create another 'dummy' field to force an aggregation of all (selected) records, but it does work... see the attachment for details.

In essence, what you do is:

Create a new field - I have named this "All" - and ensure that all values in this field are the same (I have left them blank, but it might make more 'sense' if these were all "Y")
Set the Bar view to Aggregate on the "All" field - because all of these values are the same, it will aggregate across all records in the Bar view (exactly which records this view shows will depend on the Data subset - in the attached example this is the Filtered (IN) set as you had previously)
Change the aggregation method for your formula field from Sum to [Formula Result] - this means that Omniscope will recalculate the formula based on the 'new' aggregated records... and these can use Sum / Maximum / Minimum / Mean etc. to derive total values for their component parts, so...
Ensure that the Sales and FTE columns are set to aggregate using Sum - this way the Sales and FTE values which will be used by your Sales/FTE formula will be the totals for all records in the Bar view

By the way, your Sales/FTE formula can be expressed more simply as just Sales/FTE - there is no need for the Sum statements as these will sum multiple fields (as long as you specify these fields as parameters for the Sum statements) rather than records.

stefan · April 24, 2010 3:58PM

I've actually found a better way of doing it - by using a weighted mean.

Keep the simple formaul Sales/FTE as just Sales/FTE. Then for this field select default function 'mean' and once selected within the bar view select weighted by 'FTE'.

However, am sure the more complex dummy field and aggregation trick will be useful in more complicated situations though.

stefan · April 24, 2010 4:00PM

Guy -

but thanks for your file - it was only by looking at how to do the aggregation I found the weighted by option!

Stefan

April 24, 2010 11:23PM

No problem - you're welcome! My advice is to be careful with weighted means - they work fine with simple formulae and when ALL records have data, but you can get unexpected (or downright erroneous) results when you have null values in your data. Formula result guarantees that the base data is aggregated first, and then the formula applied after. That said, I do use weighted means when I KNOW that the data will behave "correctly".

steve · August 8, 2012 12:41PM

Related idea: http://forums.visokio.com/discussion/868/is-there-a-way-to-make-subset-work-on-filtered-datas/p1

Welcome!

Categories

Ideas Parade

Tagged