2 Replies Latest reply: Jan 11, 2018 9:55 AM by Stefan van Diepen RSS

    Issues with summing a calculated field in a pivot

    Stefan van Diepen

      Dear Qlikview Experts,

       

      I'm struggling with the following situation (simplified for the example).

       

      I have 4 shipments, some sent to one city, some sent to several cities (split delivery).

      There is one table with the shipment number, the city and the quantity shipped (left table below).

      There is another table with the shipment number and the invoiced amount in total (right table below).

       

       

      Both are separately loaded into Qlikview, creating this:

       

       

      I created three tables (see below).

       

      Table 1 shows the loaded data.

      As you can see, the Invoiced amount for shipment 101 is displayed in full at both Amsterdam and Rotterdam.

       

      In Table 2, I used the formula =sum(EUR)*(sum(KG)/sum(Total<Shipment>KG)) to created a split in Invoiced amount based on KG.

      So far, so good, as it shows exactly what I want it to show.

       

      In Table 3, I set up a pivot to show me the totals per City.

      However, the totals are wrong.

      Amsterdam, for example, should be, based on Table 2: 15.000 + 17.500 + 5.000 = 37.500.

      I've tried several approaches using aggr() and distinct(), but I just can't get it to work.

      Can someone help me out here? I've attached the Qlikview Document for your convenience.

       

      Thanks in advance!

       

      Kind regards,

       

      Stefan