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

    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?

       

       

      Thanks in advance!

       

      Corinna

        • Re: Aggregation - Average
          Stefan Wühl

          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)Average.PNG

                • Re: Aggregation - Average
                  Stefan Wühl

                  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?

                       

                      Thanks in advance