Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

3 Replies
MVP
MVP

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*D2Smiley Very Happy11)*(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.

Not applicable

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

MVP
MVP

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.