Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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.

Thanks in advance!

Kind regards,

Stefan

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

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

View solution in original post

Contributor III
Contributor III

Dear Marcus,

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

Kind regards,

Stefan