Weighted Averages (Excel SUMPRODUCT)

[#1]

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

[jagan]

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.

[#3]

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

[jagan]

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.