Data Modelling Problem/Question

[#1]

Hi,

I'm dealing with a data modelling issue. Currently Invoices are analysed by MA and MG.

Data model:

Pivot table should look like this:
(Currently everythings working fine without planning figures). Selecting month and year should be possible.

MA MG Sales Figures Plan Figures
==========================================
XY X1 1000 1500
X2 2000 2110
X3 5000 4110
X4 6000 3110
XZ X1 ......

now the table plan figures is added. Can someone tell what would be the best way.

My ideas:

1. Concatenating PlanFigues and Invoices (to get "one date" field to select)
2. mapping Article_Group into Article.

Thanks for your ideas and help.

Aloah

[#2]

Hi,

Arent you interesting only in a Date in Invoices? What is the date in PlanFigures for?

If you really need to have one date for Invoices and PlanFigures then you can create calendar:

Calendar:
load
Plan_Key,
Invoices.Date as CalendarDate
resident Invoices;
drop field Invoices.Date from Invoices;
concatenate (Calendar)
load
Plan_Key,
PlanFigures.Date as CalendarDate
resident PlanFigures;
drop field PlanFigures.Date from PlanFigures;

[#3]

Hi,

I need the date field for selection months. e.g. I would like to compare the "current" figures for aug09 and the plan figures for aug 09.
this should be done in a pivottable with MA and MG as dimensions.

[#4]

What is the relationship between PlanFigures and Invoices? Those tables are already linked via Plan_Key. If you currently cannot do your comparison then this association is incorrect. We should link you Plan with Sales in the way that you will be able to do comparisons. Please attach your qvw and we will solve your problem.

[#5]

Hi,

Invoices contain the "invoices head", invoices pos the different positions of the invoice.
Generally I would agree that plan has to be linked to invoice_pos, but there's no key.
Although if it would be linked to invoice_pos I still would get an circular reference error because of MG and MA in tables Plan, Department and Group), or did I get something wrong?

Regards

[#6]

Ok so explain me one more thing. Is your plan on monthly/weekly/daily/yearly basis? So your invoice date is for example 05/05/2009, do you have a plan for that day for a given MG and MA, or is there a plan for 05/2009 only, or 2009 only?

[#7]

Plandate is on monthly basis, invoice date is on daily basis

Plantable looks like this

KEY MA MG Date Plan_Sales
=========================================
10 XY X1 Jan 09 1000
10 XY X2 Jan 09 1220
10 XY X3 Jan 09 1330
10 XY X4 Jan 09 1400
...... to Dec 09
20 XY X1 Jan 09 1222
..... to Dec 09

[#8]

Ok. I would link PlanFigures to Invoice_Pos which requires the following steps:
1. Move Date from Invoices to Invoice_Pos:
left join (Invoice_Pos)
load
InvoicePos_Key,
monthstart(Date) as InvoicePos_Date
resident Invoices;
2. Move MG to Invoice_Pos as InvoicePos_MG
3. Move MA to Invoice_Pos as InvoicePos_MA
4. Create new field in Invoices_Pos that will be a link to PlanFigures:
left join (Invoice_Pos)
load
InvoicePos_Key,
trim(InvoicePos_Date)&'-'&trim(InvoicePos_MG)&'-'&trim(InvoicePos_MA) as J_PlanFigures //after testing replace with autonumberhash...
resident Invoice_Pos;
5. create J_PlanFigures in PlanFigures table:
left join (PlanFigures)
load
Plan_Key,
trim(monthstart(Date))&'-'&trim(MG)&'-'&trim(MA) as J_PlanFigures //after testing replace with autonumberhash...
resident PlanFigures;

That should give you a proper link between sales information and target (plan).

If you have more issues with that I really suggest to post this document...