How can I do this calculation?

[rcandeo]

I have to make a calculation that shows me for each sales order, how much is the freight charge.

The problem is that for each transportation made, I can find more than one sales order associated, and when it occurs, I have to calculate the corresponding freight charge according with the weight of the products for the sales order, and I have no idea how can I do this using Qlik View.

Here are the tables that I have:

Freight Document

• Freight Number (key)
• Amount
• Weight

Sales Order

• Order Number (key)
• Freight Number (key)
• Weight

In a normal application, I'd read all sales order for each freight document, and make the prorate according to the sales order weight to find the corresponding amount, and then I could know the corresponding freight amount for each sales order.

Anyone can give me a help to do that?

[#2]

I'm not sure I completely understand, but I'm thinking that once joined, you will essentially have a table with 5 fields, two of those being weight. Multiple Sales make up each Freight. Is the Freight Amount a simple calculation (i.e. 1 kg = a certain dollar amount or half the weight pays half the freight)?

If you have a freight with 3 sales, then that freight has a weight of those three sales weights combined. From that, you can get a percentage of the freight, which you could multiply against the freight amount to get amount for each sale.

In a chart:

Sales Order (Dim)

Freight Number (Dim)

Freight Weight (Dim)

Frieght Amount (Dim)

Percentage of Freight ( Weight / "Freight Weight")

Amount for this Sale ( "Percentage of Freight" * "Freight Amount")

Am I missing a piece?

[rcandeo]

I should have made an example, and I found out that I don't need Weight field in Freight Document Table, sorry. Here is an example:

Freight Document table

Freight Number = 200

Amount = $5,000

Weight = 10,000 kg

Sales Order Table Record number 1

Order Number = 11

Freight Number = 200

Weight = 8,000 kg

Sales Order Table Record number 2

Order Number = 12

Freight Number = 200

Weight = 1,000 kg

Sales Order Table Record number 3

Order Number = 13

Freight Number = 200

Weight = 1,500 kg

The calculation that I have to do is:

Sales Order Weight / sum(Sales Order Weight) * Amount

For Order Number 11 = 8,000 / 10,500 * 5,000 = 3,809.52

My result must be:

Order Number 11 = 3,809.52

Order Number 12 = 476.19

Order Number 13 = 714.29

[#4]

I think you need an aggr() function to calculate the total weight of the freight. Aggr( Sum(Weight), Freight Number)

I'm going to plug your numbers into a simple app and try to put together a sample.

[johnw]

This maybe?

sum(Amount) * sum(Weight) / sum(total <"Freight Number"> Weight)

[#6]

That seems to do it John. Is that considered Set Analysis? I haven't seen brackets with only a field name used like that before.

I tried using this: =(Weight / Aggr(Sum (Weight), FreightNumber)) * Amount but that only seemed to work for one field.

[rcandeo]

I think the answers will help me too much. The only thing is that I need to find out where I put this calculation, since I have a huge application generating a QVD file, and I need to make this calculation in the script, where I have the sales order number and need to get the freight amount.

[johnw]

It's not set analysis. You're allowed to total across the value of a particular field or list of fields. It's something I tend to forget and have to keep reminding myself of.

sum( [{set_expression}] [ distinct ] [ total [<fld {, fld}>] ] expression )

[#9]

I think doing it in the script would be easier. Are you reading these from a database table? If so, you could use a nested SQL statement.

SELECT SalesNumber, FreightNumber, Weight, TotalFreightWeight

FROM SalesTbl INNER JOIN (SELECT FreightNumber, Sum(Weight) as TotalFreightWeight FROM SalesTbl GROUP BY FreightNumber) As FreightWeight

ON SalesTbl.FreightNumber = FreightWeight.FreightNumber

Something like that, but I haven't been hand writing a lot of SQL lately.

[#10]

Thanks John! This one is going on a note on my bulletin board.