Return the string portion of a dual after the rounded average of a field

[#1]

I am working with financial security Ratings.  The field SP_dual is set up as

[1,AAA

2,AA

....

]

I am using minstr(sp_dual) successfully to return AAA based on the underlying numbers.  My problem is now that I want to run an average (or weighted avg) and return the closest string.  i.e. the Avg returns 1.1 and I want to return AAA.  I've tried using the round() function to get it to a number that should have a string value.  I've tried re-using the applymap function in the expression but it was only returning nulls.

any ideas?

[swuehl]

Yes, numeric functions like avg() and round() will replace the text representation, so your dual text definition is lost.

And you can't use applymap in a chart.

But you can maybe try something like attached, keeping your dual definitions in a table and using some set analysis with advanced search to filter the next smaller text representation according your avg(Dual) value.

[#3]

=MinString(if(SP=$(=ROUND(AVG(SP))), SP))

[swuehl · Accepted solution]

My above suggested solution won't work in a chart on dimension level (I think neither does the second suggested expression).

But if you keep your Dual definition in a separate table, you can iterate over the values using advanced aggregation.

[#5]

Would you be able to explain the Set Analysis you used in the Text box?

     only({1<Num = {$(=max({1<Num = {'<=$(=avg(Dual))'}>}Num))} >} Text)

But thank you very much, I've been able to translate the second option for my uses.  Now I need to figure out how to make it work with dynamic dimensions.

[swuehl]

Sure, Mark.

$(=avg(Dual))

is returning the average value of field Dual (but in global context, due to the dollar sign expansion, not considering any dimensions). Let's say, it's returning 6.25

$(=max({1<Num = {'<=6.25'}>}Num))

is returning the maximum value (disregarding all selections) of field Num, where you limit Num to values <=6.25.

So, in my sample file with Num ranging 1 to 10, it will first limit field values Num to 1,2,3,4,5,6 then return the max value, i.e. 6.

only({1<Num = {6} >} Text)

is then returning the Text value for the corresponding value 6 in Num field, i.e. 'Six'.

The method of finding the max value of a set of values smaller than a threshold has the advantage that you can also use non equi-distant series of values for Num, e.g. 1,2,3,4,5,7,10,15, it should still return the next smaller value according to calculated average value. You can also get the next larger value by simply changing max to min and <= to >=.

If you have only equi-distant values in your set, you can probably work out a solution with just a rounded average in your set modifier.

[#7]

Thank you very much.  That actually answers another semi-problem I started to run into where some of the numbers were not whole numbers.  Will this run quicker than a round?  Including the aggr function just destroyed performance of the expression.  granted, it's a nasty expression.  See below (this doesn't include the conversion back to string as that conversion destroys the performance):

=Pick(Match(vRatings_Methodology,'Lower of S&P,Moody',

  'Lower of S&P,Moody,Fitch',

  'Higher of S&P,Moody',

  'Higher of S&P,Moody,Fitch'),

  Pick(Match(vRatings_Display,'S&P','Moody'),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangeminString(SP_dual,Moody_dual_asSP)),

  sum(RangeminString(SP_dual,Moody_dual_asSP)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  ),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangeminString(SP_dual_asMoody,Moody_dual)),

  sum(RangeminString(SP_dual_asMoody,Moody_dual)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  )

  ),

  Pick(Match(vRatings_Display,'S&P','Moody'),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangeminString(SP_dual,Moody_dual_asSP,Fitch_dual_asSP)),

  sum(RangeminString(SP_dual,Moody_dual_asSP,Fitch_dual_asSP)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  ),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangeminString(SP_dual_asMoody,Moody_dual,Fitch_dual_asMoody)),

  sum(RangeminString(SP_dual_asMoody,Moody_dual,Fitch_dual_asMoody)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  )

  ),

  Pick(Match(vRatings_Display,'S&P','Moody'),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangemaxString(SP_dual,Moody_dual_asSP)),

  sum(RangemaxString(SP_dual,Moody_dual_asSP)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  ),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangemaxString(SP_dual_asMoody,Moody_dual)),

  sum(RangemaxString(SP_dual_asMoody,Moody_dual)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  )

  ),

  Pick(Match(vRatings_Display,'S&P','Moody'),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangemaxString(SP_dual,Moody_dual_asSP,Fitch_dual_asSP)),

  sum(RangemaxString(SP_dual,Moody_dual_asSP,Fitch_dual_asSP)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  ),

  Pick(Match(vRatings_Aggregation,'Linear','WARF'),

  avg(RangemaxString(SP_dual_asMoody,Moody_dual,Fitch_dual_asMoody)),

  sum(RangemaxString(SP_dual_asMoody,Moody_dual,Fitch_dual_asMoody)*$(vRatings_WeightedField))/sum($(vRatings_WeightedField))

  )

  )

  )