Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rberendsen
New Contributor II

Split budget in months

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!

Tags (3)
1 Solution

Accepted Solutions
sibusiso90
Contributor II

Re: Split budget in months

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

];

2 Replies
sibusiso90
Contributor II

Re: Split budget in months

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

];

Highlighted
sibusiso90
Contributor II

Re: Split budget in months

This is an example of what I did.