Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

];

View solution in original post

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

];

View solution in original post

Highlighted
sibusiso90
Contributor II

Re: Split budget in months

This is an example of what I did.