topic Re: Unable to get syntax correct when using aggr with set analysis expression in QlikView

Unable to get syntax correct when using aggr with set analysis expression

cpetti04 — Wed, 25 Nov 2020 16:16:04 GMT

I am fairly inexperienced with qlikview so forgive my ignorance. I have trying to resolve an issue with row totals on a pivot table being incorrect. I found other solutions to state that this can be resolved by using the aggr function however I cannot seem to get syntax write on the expression used for these totals. Below is example of existing expression.

if(mydata.type='PERSONS',sum({<NAME_CODE={'VI'},approvals.APPROVAL_LEVEL={">1"},DISPOSITION={"*"}-{'1'},case.PART={'1','2'}>}CNT),
COUNT({<CHRGCNT={'1'},approvals.APPROVAL_LEVEL={">1"},DISPOSITION={"*"}-{'1'},case.PART={'1','2'}>}DISTINCT LWMAINID))

I have tried adding in a few places but always highlights as incorrect.

Re: Unable to get syntax correct when using aggr with set analysis expression

tincholiver — Mon, 18 Feb 2019 23:58:13 GMT

just add

aggr(
if(mydata.type='PERSONS',sum({<NAME_CODE={'VI'},approvals.APPROVAL_LEVEL=
{">1"},DISPOSITION={"*"}-{'1'},case.PART={'1','2'}>}CNT),
COUNT({<CHRGCNT={'1'},approvals.APPROVAL_LEVEL={">1"},DISPOSITION={"*"}-{'1'},case.PART={'1','2'}>}DISTINCT LWMAINID)),
yourdimension1,yourdimension,etc)

those dimensions are those that accumulate the values and must be in the dimensions of the graph

Re: Unable to get syntax correct when using aggr with set analysis expression

sunny_talwar — Tue, 19 Feb 2019 12:57:59 GMT

What all dimensions do you have in your chart... assuming your dimensions are dim1 and dim2... you can try this

Sum(Aggr(
  If(mydata.type = 'PERSONS',
    Sum({<NAME_CODE={'VI'},approvals.APPROVAL_LEVEL={">1"},DISPOSITION={"*"}-{'1'},case.PART={'1','2'}>}CNT),
    Count({<CHRGCNT={'1'},approvals.APPROVAL_LEVEL={">1"},DISPOSITION={"*"}-{'1'},case.PART={'1','2'}>}DISTINCT LWMAINID)
  )
, Dim1, Dim2))

Re: Unable to get syntax correct when using aggr with set analysis expression

cpetti04 — Fri, 22 Feb 2019 15:35:07 GMT

Thank you very much this resolved my issue appreciate quick response.