topic Re: Pivot Table -Total in QlikView

Pivot Table -Total

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

Hi,

In my pivot table showing wrong total.

If You can see in the screen shot, its showing the total as 338 in the chart.If you add the totals from each column in the pivot table, you get 354. Not sure why they don't match . please help me to fix it.

I have taken KS_cRIME_TYPE as dimension and

=if(LWCHRG.OPPD_PERS_PROP='PERSONS',sum({<NAME_CODE={'VI'},PDRMSBKUP_LWMAIN.APPROVAL_LEVEL={">1"},CASE_DISPOSITION={"*"}-{'1'},LWCHRG.OPPD_CRIME_PART={'2'}>}CRIME_CNT),

COUNT({<CHRGCNT={'1'},PDRMSBKUP_LWMAIN.APPROVAL_LEVEL={">1"},CASE_DISPOSITION={"*"}-{'1'},LWCHRG.OPPD_CRIME_PART={'2'}>}DISTINCT LWMAINID))

If i converted to straight table and if i selected sum of rows in the expression then getting 354.But i need to get 354 in pivot table and line charts only their we dont have the sum of rows.please help me to fix it.

Re: Pivot Table -Total

sunny_talwar — Wed, 05 Oct 2016 09:18:06 GMT

Try with Sum(Aggr(....))

Sum(Aggr(

If(LWCHRG.OPPD_PERS_PROP='PERSONS',sum({<NAME_CODE={'VI'},PDRMSBKUP_LWMAIN.APPROVAL_LEVEL={">1"},CASE_DISPOSITION={"*"}-{'1'},LWCHRG.OPPD_CRIME_PART={'2'}>}CRIME_CNT),

COUNT({<CHRGCNT={'1'},PDRMSBKUP_LWMAIN.APPROVAL_LEVEL={">1"},CASE_DISPOSITION={"*"}-{'1'},LWCHRG.OPPD_CRIME_PART={'2'}>}DISTINCT LWMAINID))

,KS_CRIME_TYPE))

Add the complete dimension name here in place for KS_CRIME_T. I couldn't see the whole name in the image, so added what I can see

Re: Pivot Table -Total

jyothish8807 — Wed, 05 Oct 2016 09:22:05 GMT

Hi Bhavvi,

You need to use advanced aggr in this case to get the actual count.

sum(aggr(if(LWCHRG.OPPD_PERS_PROP='PERSONS',sum({<NAME_CODE={'VI'},PDRMSBKUP_LWMAIN.APPROVAL_LEVEL={">1"},CASE_DISPOSITION={"*"}-{'1'},LWCHRG.OPPD_CRIME_PART={'2'}>}CRIME_CNT),

COUNT({<CHRGCNT={'1'},PDRMSBKUP_LWMAIN.APPROVAL_LEVEL={">1"},CASE_DISPOSITION={"*"}-{'1'},LWCHRG.OPPD_CRIME_PART={'2'}>}DISTINCT LWMAINID)),KS_cRIME_TYPE))

Hope this helps.

Regards

Re: Pivot Table -Total

bhavvibudagam — Wed, 05 Oct 2016 12:36:10 GMT

Thanks Jyothish. Its working

Re: Pivot Table -Total

jyothish8807 — Wed, 05 Oct 2016 12:41:41 GMT

You are welcome

Regards