Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this:
avg(rangemin(105, Percent))
Regards,
Michael
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.
not much different:
avg(rangemin(1.05, A/B))
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))))
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
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
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
Spot on .. thank you Michael
.. and thank you too Gysbert