Where's the best place to manipulate data?

[Anonymous]

I am working on a EOQ project for inventory and am pulling data from a JDE DB. I am very new to Qlikview andam confused/curious about where or how you can manipulate data with formulas. For my EOQ formula I need:

D, Ave Annual Demand

S, Setup Time

I, Carrying Cost

C, Unit cost

All of which should be linked via common Item number and Branch number. I can calculate Ave Annual demand based off of a sales table within my JDE DB, and I am using a formula to get from each individual sale to ave annual demand. Similarly, my setup time is determined by averging setup times from another JDE table. Unit cost can be pulled from the same table as the Sales. Also, I built and connected an excel file that relates branch number to respective carrying cost.

So my question is:

How can I combine all of these values into one formula that looks like this, (2DS/IC)^(1/2), that is lined to both Item Number and Branch Number?

Should I join them all into one table and apply the formula there?

or

Should I try to write the formula in the expression page of the chart wizard?

or

Is the an alternate location or method in which one can apply mathematical formulas?

Thanks.

[#2]

Hello again,

your star schema looks great: "Itemledger" as FactsTable and some DimensionTables around it. If you like (not necessary), you can join (better world would be "merge", as a join in QV is not the same as a SQL-Join) tables like "UnitPrice" into "ItemLedger" using "Left join (ItemLedger)" at the beginning of the corresponding LOAD-Statement. When joining: Take care of fitting keyvalues in the common keyfield, otherwise you may get a different (and possible incorrect) amount of rows after the join.

And your script isn't poorly written and you did it right.

So you should be able to start with an expression in a chart (let's start with a straighttable to see the results as values).

Any selections should now work and for your maths in your formulas the "coming from" of your fields is independed from any table because your data modell works fine for me.

As a second step check out performance with more rows. May be you need to transfer your maths into script. This is of course better way, but I prefer starting it in charts and transfer the expression whereever meaningful.

So, now it is time for sunday evening meal.

RR

P.S.

I missed the Branch Number you mentioned in your first post. Is it the "Business Unit" ? 🙂