Average of capped aggregation expression required

[#1]

Hi,

I want to produce an average by day of a calculated % by interval. To complicate matters though, where the % of any interval exceeds 105% I want to cap it at this level so as not to skew the result.

e.g.

1/11/2012     8:00     120%

1/11/2012     8:30     95%

1/11/2012     9:00     100%

I want to get 100% as a daily result

[Anonymous]

Try this:
avg(rangemin(105, Percent))

Regards,
Michael

[#3]

Hi,

Sorry, I didn't make myself clear.  I still have to calculate the % as well.

1/11/2012     8:00     36     30

1/11/2012     8:30     19     20

1/11/2012     9:00     17     17

I need an expression that will divide column C by column D and where the result is > 1.05 then it should be capped and then I need an average of these results as a daily total.

[Anonymous]

not much different:

avg(rangemin(1.05, A/B))

[#5]

This is my division

((SUM(INTERVAL_CMS_availtime)+SUM(INTERVAL_CMS_IEXAHT_time)+SUM(INTERVAL_CMS_auxtime6)+SUM(INTERVAL_CMS_auxtime8))/3600)/(SUM(Forecast_hours))

This is valid

RANGEMIN(1.05, ((SUM(INTERVAL_CMS_availtime)+SUM(INTERVAL_CMS_IEXAHT_time)+SUM(INTERVAL_CMS_auxtime6)+SUM(INTERVAL_CMS_auxtime8))/3600)/(SUM(Forecast_hours)))

but produces an error in expression if I put AVG (...) around it

and I cannot get this to work

AVG(TOTAL <DAILY_Date> IF(((SUM(INTERVAL_CMS_availtime)+SUM(INTERVAL_CMS_IEXAHT_time)+SUM(INTERVAL_CMS_auxtime6)+SUM(INTERVAL_CMS_auxtime8))/3600)/(SUM(Forecast_hours))>1.05,

1.05,((SUM(INTERVAL_CMS_availtime)+SUM(INTERVAL_CMS_IEXAHT_time)+SUM(INTERVAL_CMS_auxtime6)+SUM(INTERVAL_CMS_auxtime8))/3600)/(SUM(Forecast_hours))))

[Anonymous]

If you use an aggregation function (sum) inside another aggregation function (avg), you must use aggr(), probably by date in your situation.  E.g.:

avg(aggr(sum(Value), Date))

Regards,
Michael

[#7]

AVG(AGGR(((SUM(INTERVAL_CMS_availtime)+SUM(INTERVAL_CMS_IEXAHT_time)+SUM(INTERVAL_CMS_auxtime6)

+SUM(INTERVAL_CMS_auxtime8))/3600)/(SUM(Forecast_hours)),interval))

This works as an expression and is pretty much what I had myself to begin with.

It doesn't however cater for capping each interval where the result exceeds 1.05

[Anonymous · Accepted solution]

AVG(

AGGR(

rangemin(1.05,

((SUM(INTERVAL_CMS_availtime)+SUM(INTERVAL_CMS_IEXAHT_time)+SUM(INTERVAL_CMS_auxtime6)

+SUM(INTERVAL_CMS_auxtime8))/3600)/(SUM(Forecast_hours))

)

,interval)

)

Regards,

Michael

[#9]

Spot on .. thank you Michael

.. and thank you too Gysbert