Split budget in months

[rberendsen]

Dear experts,

After reading a lot of tips from this community, there are just a few things which I can't figure out.

I'm currently struggling with the following:

Budget: 
CrossTable (Year, Budget, 2)
LOAD
    Office,
    EmployeeID,
    "2003",
    "2004",
    "2005",
    "2006",
    "2007"
FROM [lib://datasources/Budget.xls]
(biff, embedded labels, header is 1 lines, table is Sheet1$);

I would like to split the Budget of every EmployeeID in months per year (for example: EmployeeID 2 has 2.000 in 2004, which means 166,67 per month). I've also loaded a Master Calendar, it would be awesome if I could fit this with my Master Calendar to eventually compare Order from EmployeeID with Budget from EmployeeID.

What is my next step? Thanks in advance for your help!

[sibusiso90 · Accepted solution]

T1:

CrossTable(Years, Budget, 2)

LOAD Office,

     EmployeeID,

     [2003],

     [2004],

     [2005],

     [2006],

     [2007]

FROM

(biff, embedded labels, header is 1 lines, table is Sheet1$);

T2:

LOAD

Office

,EmployeeID

,Years

,Budget/12

,Budget

Resident T1;

drop table T1;

Left Join (T2)

load

Month

inline

[

Month,

JAN,

FEB,

MAR,

APR,

MAY,

JUN,

JUL,

AUG,

SEP,

OCT,

NOV,

DEC

];

[sibusiso90]

This is an example of what I did.