topic Re: Aggregation - Average in QlikView

Aggregation - Average

Mon, 13 Aug 2012 12:06:25 GMT

Hey guys,

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

Application	Severity	Count(Incident)	Count(Out_of_sla)	1-(Count(out_of_sla)/Count(incident)
xyz	0,5	6	2	0,67
xyz	0,3	4	4	0
xyz	0,1	4	1	0,75
xyz	0,1	4	0	1

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?

Thanks in advance!

Corinna

Re: Aggregation - Average

swuehl — Mon, 13 Aug 2012 12:30:43 GMT

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

Tue, 14 Aug 2012 07:04:54 GMT

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

swuehl — Tue, 14 Aug 2012 08:56:09 GMT

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

Thu, 16 Aug 2012 07:23:01 GMT

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?

Thanks in advance