topic Re: Average of capped aggregation expression required in QlikView

Average of capped aggregation expression required

Wed, 28 Nov 2012 15:22:01 GMT

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

Re: Average of capped aggregation expression required

Anonymous — Wed, 28 Nov 2012 15:31:27 GMT

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

Regards,
Michael

Re: Average of capped aggregation expression required

Wed, 28 Nov 2012 15:42:52 GMT

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.

Re: Average of capped aggregation expression required

Anonymous — Wed, 28 Nov 2012 15:44:41 GMT

not much different:

avg(rangemin(1.05, A/B))

Re: Average of capped aggregation expression required

Wed, 28 Nov 2012 16:00:21 GMT

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))))

Re: Average of capped aggregation expression required

Anonymous — Wed, 28 Nov 2012 16:04:55 GMT

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

Re: Average of capped aggregation expression required

Wed, 28 Nov 2012 16:10:20 GMT

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

Re: Average of capped aggregation expression required

Anonymous — Wed, 28 Nov 2012 16:15:06 GMT

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

Re: Average of capped aggregation expression required

Wed, 28 Nov 2012 16:20:23 GMT

Spot on .. thank you Michael

.. and thank you too Gysbert