Equal distribution of a yearly budget over 12 month

[Anonymous]

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):

AccountNo	CostCenter	Budgetdate	Amount
10000	100	01.01.2017	500,00
10000	200	01.01.2017	200,00
10000	300	01.01.2017	300,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:

AccountNo	CostCenter	Budgetdate	Amount
10000	100	01.01.2017	41,66
10000	100	01.02.2017	41,66
....	.....	.....	.....

Thank you in advance for any suggestions and help!

Best regards,

Roman

[sunny_talwar]

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

[sergio0592 · Accepted solution]

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

];

[Anonymous]

This works great, thank you so much!