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.

       

      Thanks