topic How can I do this calculation? in QlikView

How can I do this calculation?

rcandeo — Thu, 28 May 2009 02:36:45 GMT

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?

How can I do this calculation?

Thu, 28 May 2009 02:54:39 GMT

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?

How can I do this calculation?

rcandeo — Thu, 28 May 2009 03:14:25 GMT

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

How can I do this calculation?

Thu, 28 May 2009 03:18:53 GMT

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.

How can I do this calculation?

johnw — Thu, 28 May 2009 03:24:22 GMT

This maybe?

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

How can I do this calculation?

Thu, 28 May 2009 03:41:02 GMT

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.

How can I do this calculation?

rcandeo — Thu, 28 May 2009 03:45:40 GMT

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.

How can I do this calculation?

johnw — Thu, 28 May 2009 03:47:14 GMT

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 )

How can I do this calculation?

Thu, 28 May 2009 03:55:26 GMT

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.

How can I do this calculation?

Thu, 28 May 2009 03:56:31 GMT

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

How can I do this calculation?

johnw — Thu, 28 May 2009 04:07:05 GMT

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]
;

How can I do this calculation?

rcandeo — Thu, 28 May 2009 21:23:03 GMT

Thank You Very Much, I learned a lot with the help that all of you gave me, and I've got the number I need in my script now!