Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Issues with summing a calculated field in a pivot

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.

Kind regards,

Stefan

1 Solution

Accepted Solutions

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)

resident ShipmentData group by Shipment;

- Marcus

2 Replies

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)

resident ShipmentData group by Shipment;

- Marcus

Anonymous
Not applicable
Author

Dear Marcus,

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

Kind regards,

Stefan

Community Browser