topic Re: Pivot table total not working correctly in App Development

Pivot table total not working correctly

rajatsingh_40 — Sat, 16 Nov 2024 04:05:11 GMT

Hi Guys,

I am working on ageing report, where I have created 3 input boxes to get the bucket values by the user and they have by default values.
so I created a Pivot Table like this,

the problem is that the total function does not adds the column values correctly, as you can see for bucket 50-120 days and 120>days columns.
however its working fine when i convert it to straight table.
please help me in this.

I am using this expression for last column i.e 120>days,

"if(Sum(Aggr(sum( {<duedays = {">=$(Vbucket3)"}>} DebitAmount ),[region]))+sum(Aggr(sum([CreditAmount]),[region]))>=0,
Sum(Aggr(sum( {<duedays = {">=$(Vbucket3)"}>} DebitAmount ),[region]))+sum(Aggr(sum([CreditAmount]),[region]))
,0)"

Re: Pivot table total not working correctly

sunny_talwar — Tue, 19 Nov 2019 12:53:16 GMT

This might be challenging because you are using a calculated dimension... if it was a simple dimension... you could have used Sum(Aggr(YourExpression, YourDimensions)), but because it is using a calculated dimension in the chart, you cannot add this to Aggr() function's dimension....

Would you be able to share a sample where we can look at this and try out few things?

Re: Pivot table total not working correctly

rajatsingh_40 — Wed, 20 Nov 2019 05:24:28 GMT

Hi,
I am attaching the test_delete.qvf with sample data.

Re: Pivot table total not working correctly

sunny_talwar — Wed, 20 Nov 2019 12:11:19 GMT

Check attached

Re: Pivot table total not working correctly

rajatsingh_40 — Thu, 21 Nov 2019 04:18:04 GMT

Thank you for your help Sunny, Aggr() really helped me in the report.