topic Re: Weighted average in Total Row in a pivot table in QlikView

Weighted average in Total Row in a pivot table

phoenix — Wed, 17 Oct 2018 19:56:32 GMT

Is there a way to replace the sum in Total row with this weighted average calculation? Thanks

Re: Weighted average in Total Row in a pivot table

sunny_talwar — Wed, 17 Oct 2018 20:06:52 GMT

May be this

If(Dimensionality() = 1, Sum(AAA*HHH)/Sum(HHH), Sum(AAA))

Re: Weighted average in Total Row in a pivot table

phoenix — Fri, 19 Oct 2018 00:35:05 GMT

stalwar1‌ can you take a look at the app? trying to get the logic work in this example. Thanks

Re: Weighted average in Total Row in a pivot table

Sergey_Shuklin — Fri, 19 Oct 2018 06:46:28 GMT

Hello!

You can use special calculating condition IF RowNo() = 0.

Re: Weighted average in Total Row in a pivot table

sunny_talwar — Fri, 19 Oct 2018 10:45:09 GMT

Try this

If(Dimensionality() = 1,

Sum(AVG_BAL * If(Mod(RPT_PRD_DATE_YR, 4) = 0 and Mod(RPT_PRD_DATE_YR, 100) <> 0, 366, 365)/RPT_DAYS)/Sum(Aggr(Only(If(Mod(RPT_PRD_DATE_YR, 4) = 0 AND Mod(RPT_PRD_DATE_YR, 100) <> 0, 366, 365)/RPT_DAYS), RPT_DATE_YR, RPT_DATE_MTH)),

Sum(AVG_BAL))

Re: Weighted average in Total Row in a pivot table

phoenix — Fri, 19 Oct 2018 13:00:24 GMT

Thanks stalwar1‌ that worked