Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I simply want to create an expression that averages the sum of two fields (DA_Long + DA_Short), but I would like to EXCLUDES instances where the DA_Long+DA_Short is 0. Alternatively I simply want to average where DA_Long+DA_Short is NOT 0.0.
However, the below does not appear to work.
AVG({$<DA_Long={"<>0.0"}, DA_Short={"<>0.0"}>} DA_Long + DA_Short)
Is there another way of doing this?
=avg( if( rangesum(DA_LONG,DA_Short), rangesum(DA_LONG,DA_Short) )
Thank you Swuehl
Does RangeSum igonore nulls? I don't want to include records where nulls are interpreted as 0 and the 0 contributes towards the average. I simply want to exclude records where DA_Long+DA_Short are 0 or Null.
The rangesum() will treat NULL evaluated arguments zero values. But I think most important is that the ELSE branch is NULL and won't contribute to the average(). So regardless if DA_Long and / or DA_Short are zero or NULL, as long as the rangesum() is not different from zero, it will not contribute. But, maybe I am misunderstanding your request, instead of the rangesum(), you can use the plain arithmetic sum:
=avg( if( DA_Long+ DA_Short <> 0, DA_Long+DA_Short) )
If you want increase performance, I would suggest creating the sum of your two fields in the script, then use the summed field
LOAD
DA_Long,
DA_Short,
if(DA_Long + DA_Short <> 0, DA_Long + DA_Short, NULL() ) as SummedField,
...
avg( SummedField)
Thanks swuehl,
I have found an alternative to rangesum, using set analysis where Positions={">0 <0"} which is the same as DA_Long+DA_Short<>0.
AVG({$<DashAssetClass={'EQUITY'}, Position={">0 <0"}>} aggr(SUM(DA_Long + DA_Short), Ladder_Date))
This works for all Dimensions (Issuers).
HOWEVER another expression I want is the RTN (see below) where the numerator is the sum of PnL and the denominator is the AVG I calculated above. The problem is when I select a few Dimensions I can see the chart and the returns. But when I want the entire universe of dimension it does not show the chart. Any idea why that may be?
SUM({$<DashAssetClass={'EQUITY'}>} TDY_PnL_Cash_Accr)/AVG({$<DashAssetClass={'EQUITY'}, Position={">0 <0"}>} aggr(SUM(DA_Long + DA_Short), Ladder_Date))
No sorry, my crystal ball is on vacation this week.
You would need to post more information on your data model and chart setting.
Thanks swuehl
Apologies for getting detailed on my specific model. I want to try to simplify and I think there are couple of issues.
Firstly, there is a subtilty here in that I want to average DA_Long+DA_Short over dates and I'am not sure this is possible for rangesum. So am I correct in using AVG(aggr(SUM(DA_Long+DA_Short), Ladder_Date))?
At the same time I want to avoid in the avg contribution where DA_Long+DA_Short=0 (or where Position=0).
Secondly and slightly unrelated, in an expression where I have a numerator and a denominator, is it acceptable to have the data set for numerator different from the denominator?