Weighted average (sumproduct)

[simona_un]

Hi,

I have this challenge where I would like to calculate the weighted average of person by exposure across the year and package:

Any idea how to solved it?

Thanks 🙂

[marksouzacosta]

Hi @simona_un,

I believe the correct Excel expression is this one right?
SUMPRODUCT(D3:D12,C3:C12)/SUM(D3:D12)

If so, this is how you can do in Qlik:

How I have loaded your data:

LOAD
  [Policy],
  [Count of person],
  [Exposure],
  [Package],
  [Year]
FROM
  [lib://DataFiles/Weighted average.xlsx]
  (ooxml, embedded labels, table is Sheet1)
;

And the expression on a KPI chart:

=Sum([Exposure] * [Count of person])/Sum([Exposure])

Is this what are you looking for?

Regards

Mark Costa