Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Equal distribution of a yearly budget over 12 month

Hello all,

I am looking for a solution to distribute a yearly budget figure over 12 months when loading a table. My goal is to compare monthly budget figures per account and cost center with the monthly actual figure per account and cost center.

The table I am loading from Access-Database looks as follows (all budget for 2017 has the date 01/01/2017):

AccountNoCostCenterBudgetdateAmount
1000010001.01.2017500,00
1000020001.01.2017200,00
1000030001.01.2017300,00
...............

Has anyone a suggestion for me how I can achieve this in the script when loading the data from the above table?

The resulting table should look like this:

AccountNoCostCenterBudgetdateAmount
1000010001.01.201741,66
1000010001.02.201741,66
...................

Thank you in advance for any suggestions and help!

Best regards,

Roman

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Equal distribution of a yearly budget over 12 month

Hi,

You can try with :

TABLE:

LOAD * ,

Amount/12 as MonthlyAmount

INLINE

[    

AccountNo,    CostCenter,    Budgetdate,    Amount

10000,    100,    01.01.2017,    500

10000,    200,    01.01.2017,    200

10000,    300,    01.01.2017,    300

];

join

LOAD

  IterNo(),

  StartMonth + IterNo() - 1 as Month

  While StartMonth + IterNo() - 1 <= EndMonth;

LOAD * INLINE

[StartMonth, EndMonth

01, 12

];

View solution in original post

3 Replies
Highlighted

Re: Equal distribution of a yearly budget over 12 month

I guess you can join with all dates in the year and divide the Amount by 12?

Highlighted
Specialist III
Specialist III

Re: Equal distribution of a yearly budget over 12 month

Hi,

You can try with :

TABLE:

LOAD * ,

Amount/12 as MonthlyAmount

INLINE

[    

AccountNo,    CostCenter,    Budgetdate,    Amount

10000,    100,    01.01.2017,    500

10000,    200,    01.01.2017,    200

10000,    300,    01.01.2017,    300

];

join

LOAD

  IterNo(),

  StartMonth + IterNo() - 1 as Month

  While StartMonth + IterNo() - 1 <= EndMonth;

LOAD * INLINE

[StartMonth, EndMonth

01, 12

];

View solution in original post

Highlighted
Contributor
Contributor

Re: Equal distribution of a yearly budget over 12 month

This works great, thank you so much!