Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Average of capped aggregation expression required

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

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Average of capped aggregation expression required

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

8 Replies
mov
Esteemed Contributor III

Re: Average of capped aggregation expression required

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

Regards,
Michael

Highlighted
Not applicable

Re: Average of capped aggregation expression required

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.

mov
Esteemed Contributor III

Re: Average of capped aggregation expression required

not much different:

avg(rangemin(1.05, A/B))

Not applicable

Re: Average of capped aggregation expression required

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

mov
Esteemed Contributor III

Re: Average of capped aggregation expression required

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

Not applicable

Re: Average of capped aggregation expression required

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

mov
Esteemed Contributor III

Re: Average of capped aggregation expression required

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

Not applicable

Re: Average of capped aggregation expression required

Spot on .. thank you Michael

.. and thank you too Gysbert

Community Browser