# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
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.

4 Replies
Contributor III

## Re: Calculate Cost per Unit - Split String?

Do you have a field with units per box?

Not applicable

## Re: Calculate Cost per Unit - Split String?

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 bags 18 24 - 12 oz bottles 19 12 - 550 ml bottles 10 48 - 6 oz jars 22 36 boxes 21.35 12 - 8 oz jars 25 12 - 1 lb pkgs. 30 12 - 12 oz jars 40 18 - 500 g pkgs. 97

Hope my problem is more clear now

Contributor III

## Re: Calculate Cost per Unit - Split String?

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

## Re: Calculate Cost per Unit - Split String?

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