SUBSET_MEAN weighted by

acm · January 22, 2010 4:09PM

Hi,

I am trying to calculate per subset the average of a variable (ROI).
I need the ROI weighted by another variable (costs). I can set the weighting in the visuals which works fine, but can I integrate this weighting in the SUBSET_MEAN function?

Thanks
Arjan

steve · January 22, 2010 4:15PM

Weighted mean isn't yet available in formulae.

Another idea - again, not yet available - is to have a SUM_FOR function (and MEAN_FOR) such as SUM_FOR(SUBSET(...), [A]*[B]) which would evaluate a sub-expression (e.g. [A]*[B]) and calculate it for every record in SUBSET(...), then take the sum of those results. This also would allow you to construct a weighted mean and other more complex formulae.

acm · January 25, 2010 2:02PM

Steve,

Will this be a feature in 2.6? See a lot of situations in which I can use this!
Would be great
Thanks

steve · January 25, 2010 2:33PM

No, 2.6 is closed to new features - sorry.

indranildatta · November 10, 2010 1:01PM

Hi,

I have a very similar requirement like above. I need to weight SUBSET_MEAN of "Percent View" with a field called Total. Could you please suggest a workaround? Would multiplying the original column with the Total column and then applying SUBSET_MEAN work?

Cheers!
Indranil.

steve · November 10, 2010 1:34PM

Yes, create a field called "Adjusted Percent View" which has formula [Total] * [Percent View]

Then use SUBSET_SUM([Adjusted Percent View]) / SUBSET_SUM([Total]).

I think this is correct.

November 13, 2010 4:27PM

From bitter experience, I would advise extreme caution when using weighted mean calculations... they can work well in some cases, but results are dependent on underlying data, aggregation choices, use of subsets and grouping. It is very easy to derive incorrect results which look 'pretty close' in many cases but can be substantially wrong. I say bitter experience because I have been caught out several times in client projects, and now resist the use of weighted means in all commercial work. For an example, see this thread - http://forums.visokio.com/comments.php?DiscussionID=437

Welcome!

Categories

Ideas Parade