Pivot Table -Total

[bhavvibudagam]

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.

[sunny_talwar]

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

[jyothish8807 · Accepted solution]

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

KC

[bhavvibudagam]

Thanks Jyothish. Its working

[jyothish8807]

You are welcome

Regards

KC