Issues with summing a calculated field in a pivot

[Anonymous]

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

[marcus_sommer · Accepted solution]

Something like this should be working:

=sum(aggr(sum(EUR)*(sum(KG)/sum(Total<Shipment>KG)), Shipment, City))

Further it might be helpful to count the number of shipments and/or further to calculate the needed values already within the script, for example with:

left join(InvoiceData)

load Shipment, count(Shipment) as ShipmentCount

resident ShipmentData group by Shipment;

- Marcus

[Anonymous]

Dear Marcus,

thanks, the formula works like a charm! Does exactly what I want it to do, great!

Kind regards,

Stefan