4 Replies Latest reply: Aug 16, 2012 3:23 AM by Corinna Mehl

Aggregation - Average

Hey guys,

I have some difficulties with using aggregations, I hope someone might help me.

ApplicationSeverityCount(Incident)Count(Out_of_sla)1-(Count(out_of_sla)/Count(incident)
xyz0,5620,67
xyz0,3440
xyz0,1410,75
xyz0,1401

Now i need a field that calculates the arithmetic average, something Like

Sum(Severity*Count(Incident)) /Sum(Count(Incident)*Sum(1-Count((out_of_sla)/Count(incident))

Does anyone know how i can implement  this in QlikView?

Corinna

• Re: Aggregation - Average

Corinna,

if you want to embed an aggregation function into another aggregation function (like a count() into a sum()), you'll need to use advanced aggregation (i.e. the aggr() function). Please check the help for the details.

If you could come up with some more details (what are your dimensions), and some sample lines of input date (e.g. in an LOAD .. INLINE [...] style), I would assume someone here should be able to help you with the exact syntax.

Regards,

Stefan

• Re: Aggregation - Average

Hi Stefan,

thank you, I already tried to use the aggr() function but to no avail. Though I don't know whether I just messed up the syntax...

My dimensions should be Application, Priority (sorry, forgot about it in my previous post) and Severity.

Each Application has incidents with prio 1-4 and each prio has a corresponding severity.

At the moment we use an excel sheet which should be transfered in QlikView. I attached the result in excel. Hope that might helps ( I need the formula for the fields "impact" and "average", whereas "impact" should be calculated as I described in my previous post)

• Re: Aggregation - Average

Corinna, could you post a sample excel file with input data and maybe also the expected outcome calculated with excel?

Or could you recheck your above formula,

Sum(Severity*Count(Incident)) /Sum(Count(Incident)*Sum(1-Count((out_of_sla)/Count(incident))

If Count(oSLA) equals count(Incidents), this denominator will return zero, is this correct? Could be that I am a little confused, I think not all opening brackets have a closing counter part.

• Re: Aggregation - Average

Hi,

I tried various combinations of sum() and aggr(), but there is one thing I still couldn't solve.

I used the following formula to create the column performance(%)

if(isNull(1-(SUM(OutSLA_Reaction)/SUM(AllIncidents))),Sum(total1),(1-(SUM(OutSLA_Reaction)/SUM(AllIncidents))))

The formula works out fine, but I want to reuse it in a second column, because I need the total performance(%), e.g. 0.97 (97%)

I tried to use something like

sum(total column(1))

and sum(aggr(column(1),PRIORITY,APPLICATION)

and various different combinations of sum() and aggr() to no avail.

My dimensions are

APPLICATION

PRIORITY

SEVERITY

NUMBERofINCIDENTS

OUTSLA_REACTION

Has anyone an idea why using sum() in combination with column(1) doesn't work?