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

Calculate Cost per Unit - Split String?

Hello,

I am using the Northwind Database (Microsoft) as an example and i am facing the following issue:

I want to calculate the Profit Margin. I do have the Sale Price per Unit. When it comes to the Cost per Unit,

the amount is for a whole box. To make it more clear i ll give an example:

Sales in Retail

Product                    Quantity              Price

Coca Cola Can              1                  $1.55

Procurement (wholesale from the distributor)

Product                                      Quantity                  Price

24 cans Coca Cola (box)                 1                        $22.00

There is inconsistency between buying from the wholesaler and selling retail.

In other words, I would like to distinguish somehow the number of products in the box(24) and then use it

to find the Cost per Can.

Please help!!!! Thank you

4 Replies
albertovarela
Partner - Specialist
Partner - Specialist

Do you have a field with units per box?

Not applicable
Author

Well yes, but it contains other info as well.

Here are a few records (Exactly as they appear in the database)

QuantityPerUnit          UnitPrice

10 boxes x 20 bags18.0000
24 - 12 oz bottles19.0000
12 - 550 ml bottles10.0000
48 - 6 oz jars22.0000
36 boxes21.3500
12 - 8 oz jars25.0000
12 - 1 lb pkgs.30.0000
12 - 12 oz jars40.0000
18 - 500 g pkgs.97.0000

Hope my problem is more clear now

albertovarela
Partner - Specialist
Partner - Specialist

I get it now. It's not consistent at all!... it won't be easy if you can't find a way to determine the conversion between wholesale and retail. Sorry for not being very helpful...

Not applicable
Author

Believe it or not this is a real world scenario (according to Microsoft) and it seems very challenging. Lets wait and see if someone has an idea. There must be a way. Thank you for your help anyways