Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to do an "Average If" to basically trim data that is too high or too low for the general smell test.
Here is my expression:
AVG(
IF(
FRACTILE([BalSht_TotalAssets_Total_assets],.05) < [BalSht_TotalAssets_Total_assets] AND [BalSht_TotalAssets_Total_assets] < FRACTILE([BalSht_TotalAssets_Total_assets],.95),
[BalSht_TotalAssets_Total_assets],
NULL())
)
This does not seem to work. I was curious if there was a better way, or if I'm doing something incorrectly.
I'd like to eventually tie this out to 2 variables. So the user can use a slider to set the fences, but defaulted to the .5 and .95.
I'd like to use it to do not only avg, but also Fractiles for 25th, 50th, and 75th
Try
AVG(
IF(
FRACTILE(TOTAL [BalSht_TotalAssets_Total_assets],.05) < [BalSht_TotalAssets_Total_assets] AND [BalSht_TotalAssets_Total_assets] < FRACTILE(TOTAL [BalSht_TotalAssets_Total_assets],.95),
[BalSht_TotalAssets_Total_assets],
NULL())
)
Try
AVG(
IF(
FRACTILE(TOTAL [BalSht_TotalAssets_Total_assets],.05) < [BalSht_TotalAssets_Total_assets] AND [BalSht_TotalAssets_Total_assets] < FRACTILE(TOTAL [BalSht_TotalAssets_Total_assets],.95),
[BalSht_TotalAssets_Total_assets],
NULL())
)
Not sure which is better, but this seemed to work for me
avg(if([BalSht_CurrentAssets_Cash_on_hand_in_banks] > aggr(NODISTINCT fractile([BalSht_CurrentAssets_Cash_on_hand_in_banks], vLTrim),[fy_end]) and [BalSht_CurrentAssets_Cash_on_hand_in_banks] < aggr(NODISTINCT fractile([BalSht_CurrentAssets_Cash_on_hand_in_banks] , vUTrim),[fy_end]),[BalSht_CurrentAssets_Cash_on_hand_in_banks] )),