3 Replies Latest reply: Jul 16, 2018 4:09 AM by Bala Bhaskar RSS

    Can not get Pivot Table totals to add correctly

    Tony Lastoria

      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.