topic Calculate Cost per Unit - Split String? in QlikView

Calculate Cost per Unit - Split String?

Thu, 23 Oct 2014 00:06:13 GMT

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

Re: Calculate Cost per Unit - Split String?

albertovarela — Thu, 23 Oct 2014 00:26:16 GMT

Do you have a field with units per box?

Re: Calculate Cost per Unit - Split String?

Thu, 23 Oct 2014 00:56:40 GMT

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.0000
24 - 12 oz bottles	19.0000
12 - 550 ml bottles	10.0000
48 - 6 oz jars	22.0000
36 boxes	21.3500
12 - 8 oz jars	25.0000
12 - 1 lb pkgs.	30.0000
12 - 12 oz jars	40.0000
18 - 500 g pkgs.	97.0000

Hope my problem is more clear now

Re: Calculate Cost per Unit - Split String?

albertovarela — Thu, 23 Oct 2014 01:40:54 GMT

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...

Re: Calculate Cost per Unit - Split String?

Thu, 23 Oct 2014 01:47:31 GMT

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