Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Split fee among products in order

Hi!

I have two tables, similar to these:

Orders:

Load * inline

[

OrderID, Fee

1, 20.0

2, 0.0

3, 12.50

4, 13.50

];

Items:

Load * inline

[

OrderID, Qty, ProductName,

1, 12, "Egg"

1, 2, "Apple"

1, 1, "Milk"

2, 1, "Egg"

2, 1, "Milk"

3, 20, "Apple"

4, 40, "Milk"

4, 10, "Apple"

];

I want to present sales per product but be able to deduct fees. However, the fees are given per order, and have to be split among the products in that particular order. How can I achieve this? I have a feeling there is an easy way to do this.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

maybe

sum(Qty*Price)

sum(aggr(sum(Fee)/count(total <OrderID> ProductName),ProductName, OrderID))

column(1) -column(2)

1.jpg

View solution in original post

4 Replies
MK_QSL
MVP
MVP

You want to Deduct Fee from Qty?

Not applicable
Author

Ah, sorry. Each product should have a price too. See new table below.

So I might want to calculate the total sales as:

sum(Qty*Price) with ProductName as the dimension.

But if I want to deduct the fees, then what should I do? Something like:

sum(Qty*Price - FeePerProduct?)

Items:

Load * inline

[

OrderID, Qty, ProductName, Price

1, 12, "Egg", 1

1, 2, "Apple", 2

1, 1, "Milk", 5

2, 1, "Egg", 1

2, 1, "Milk", 5

3, 20, "Apple", 2

4, 40, "Milk", 5

4, 10, "Apple", 2

];

maxgro
MVP
MVP

maybe

sum(Qty*Price)

sum(aggr(sum(Fee)/count(total <OrderID> ProductName),ProductName, OrderID))

column(1) -column(2)

1.jpg

Not applicable
Author

Thanks, that seems to work! And seeing that aggr function gave me the even better idea to create an OrderInfo table in the load script, using GROUP BY, which will basically calculate the total count of products and total value of each order. With those numbers on hand, it is easy to split the fee among the products later on.

Thanks again!