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

# 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.

Bruce

• ###### Re: Weighted Averages (Excel SUMPRODUCT)

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,

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

• ###### Re: Weighted Averages (Excel SUMPRODUCT)

Hi,

Try this

=Sum({<[TimeDiff Arrival- Actual Depart] ={'<241'}>} [TimeDiff Arrival- Actual Depart]) * Count({<[TimeDiff Arrival- Actual Depart] ={'<241'}>} DISTINCT MRN)

/ Sum(TOTAL [TimeDiff Arrival- Actual Depart])

Regards,

Jagan.