Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Sales Order
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?
If you're instead loading from QVDs or other existing QlikView tables, I think it gets a bit more involved, since you can only load from one table at a time. Here's one approach:
LEFT JOIN ([Freight Document])
LOAD
"Freight Number"
,sum("Weight") as "Freight Weight"
RESIDENT [Sales Order]
GROUP BY "Freight Number"
;
[Freight Unit Price]:
MAPPING LOAD
"Freight Number"
,"Amount" / "Freight Weight" as "Freight Unit Price"
RESIDENT [Freight Document]
;
DROP FIELD "Freight Weight"
;
LEFT JOIN ([Sales Order])
LOAD
"Order Number"
,"Weight" * applymap('Freight Unit Price',"Freight Number") as "Freight Amount"
RESIDENT [Sales Order]
;
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?
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
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.
This maybe?
sum(Amount) * sum(Weight) / sum(total <"Freight Number"> Weight)
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.
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.
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 )
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.
Thanks John! This one is going on a note on my bulletin board.