3 Replies Latest reply: Feb 10, 2015 3:20 AM by jagan mohan rao appala RSS

    Weighted Averages (Excel SUMPRODUCT)

      Hi,

      I have been asked to generate a report looking at Weighted Averages.

       

      I have been looking for a formula to generate the equivalent in Qlik Sense.


      I have two columns.

      1st column is time in minutes

      2nd is a client identifier



      Over a date range, the % of clients who are cared for by Speciality.



      Thanks in advance.

       

      Bruce

        • Re: Weighted Averages (Excel SUMPRODUCT)
          jagan mohan rao appala

          Hi,

           

          For getting average you can use Avg() in Qliksense, if you attach some sample data and expected output then it is easier to provide the solution.

           

           

          Excel SumProduct() - =SUMPRODUCT(B2:B11,C2:C11)

          Qlikview Equivalent = Sum(Value1 * Value2)

           

          =SUMPRODUCT((C2:C11*D2:D11)*(B2:B11="HR"))


          Qlikview Equivalent: Sum({<Dept={"HR"}>} Value1 * Value2)


          Excel Sumproduct() Example:

          consultdmw.com/excel-sumproduct-function.htm




          Hope this helps you.


          Regards,

          Jagan.

            • Re: Weighted Averages (Excel SUMPRODUCT)

              Jagan,

              Thanks for your reply.

              Below is the formula I am using

               

              Sum(total if( [TimeDiff Admit Actual Depart] <'241',[TimeDiff Admit Actual Depart]) * Aggr(count(if([TimeDiff Admit Actual Depart] < '241'), [MRN]),[MRN]) / Sum(Total Aggr(Sum([TimeDiff Admit Actual Depart]), [MRN])))

               

              this is another version I have tried.

               

               

              sum(total(count([TimeDiff Arrival- Actual Depart]<'241') * Count([MRN]))) / Sum([TimeDiff Admit Actual Depart])

               

               

               

              The result I am after is the number of MRN's who are less than 240 mins (Arrival to Depart) as a weighted average over the Current Selection.

               

              As a separate formula, I then need to show this Weighted Average  % in relation to another field "ConsCode".

               

              thanks

               

              Bruce