Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

tony_last
New Contributor

Can not get Pivot Table totals to add correctly

Hello, I'm fairly new to the Qlikview development world so any help would be appreciated.

I am troubleshooting some Pivot Table data issues where I can confirm that the details for the table are correct but when the details are added together it produces wrong totals.

Background: The model is for transportation where users are tracking movement and costs related to the movement of goods throughout our company.

I have the following calculation to create a vendor cost for each purchase order:

Sum({$<Type={'Carrier Shipment'}, [Charge Type]=, [Dest Geolocation]=>} aggr(sum([Weight-KG]), [PO Number]))*
((
if (count([PO Number])=0,0,sum({$<Type={'Carrier Charge'}, [Commodity]=, [Dest Geolocation]=,[PO Vendor Number]=>}total <[Carrier Move]> [Charge Amount])))
/(
if (count([PO Number])=0,0,sum({$<Type={'Carrier Shipment'}, [Charge Type]=, [Dest Geolocation]=,[PO Vendor Number]=>}total <[Carrier Move]> [Weight-KG]))))

I have attached a spreadsheet to illustrate the issue.

In the spreadsheet there is both the pivot table, which is incorrect, and a straight table which works fine. I would prefer to keep the pivot table for the sub-totals.

I have already tried to change the formula to use the total function rather than the aggr without success.

Any ideas would be welcomed.

Thanks

3 Replies
Arjunarao
Honored Contributor II

Re: Can not get Pivot Table totals to add correctly

Hi,

Can you attach the application as well?

Your expression is confusing as compare with the source.

tony_last
New Contributor

Re: Can not get Pivot Table totals to add correctly

Hello and thanks for your interest. Unfortunately I will be away and am unable to post anything else until I get back. I will attempt to get this restarted then.

balabhaskarqlik
Honored Contributor

Re: Can not get Pivot Table totals to add correctly

1. In your expression after *, there are two braces are there, these braces are not closed. Add 2 braces at the end.

2. Now try to add / modify the dim's as required.

Community Browser