Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
I guess you can join with all dates in the year and divide the Amount by 12?
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
];
This works great, thank you so much!