Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
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.

Please help!!!! Thank you

4 Replies
AlbertoV
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 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

AlbertoV
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

Community Browser