topic Re: Aggr expression using TOTAL in QlikView

Aggr expression using TOTAL

byrnel0586 — Fri, 24 Aug 2018 18:40:39 GMT

Hi all,

I am trying to create an expression as a calculated dimension within a pivot table. The table could have multiple PCPs listed but not selected and I'd like the expression to show an overall weighted avg value regardless of selection. I have tried using TOTAL and it works when a PCP is selected, but is aggregating all PCPs if multiple or none are selected.

=if(num(ReportDate) < '43312',
SUM(TOTAL(Aggr(
IF($(RoundedRate%)>=num#(PtileBeg) and $(RoundedRate%)<= num#(PtileEnd)
,Star) * Only(RptCardWeight)
,Star,_MeasureYearKey)))
/
SUM(TOTAL( RptCardWeight)),
round(SUM(TOTAL(Aggr(
IF($(RoundedRate%)>=num#(PtileBeg) and $(RoundedRate%)<= num#(PtileEnd)
,Star) * Only(RptCardWeight)
,Star,_MeasureYearKey)))
/
SUM(TOTAL(RptCardWeight)) ,0.1))

Any help is much appreciated.

Thank you.

Re: Aggr expression using TOTAL

vishsaggi — Fri, 24 Aug 2018 19:19:08 GMT

TOTAL Qualifier respects your dimension selections. So what are you trying to achieve. ?

Re: Aggr expression using TOTAL

byrnel0586 — Fri, 24 Aug 2018 19:27:33 GMT

I am wanting the calculation to show the overall score for each PCP regardless of selection. When I try that expression without the TOTAL it says error as a calculated dimension. It works as an expression in a straight table though.

Re: Aggr expression using TOTAL

vishsaggi — Fri, 24 Aug 2018 19:51:35 GMT

May be try like

Sum( TOTAL <PCPFieldname> .....)