Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I try to load project budget to each month between project start date and project end date.I have question on my loading script:
Here is loading script for daily budget. This one working fine.
LOAD [Proj ID] as GoalProj,
Year([Proj Start Dt]+iterno()-1) as GoalYear,
Month([Proj Start Dt]+iterno()-1) as GoalMonth,
num(Month([Proj Start Dt]+iterno()-1)) as GoalMonthNo,
Date([Proj Start Dt]+iterno()-1) as GoalDate,
[Proj Tot Value]/([Proj End Dt]-[Proj Start Dt]+1) as DailyGoalAmount
while ([Proj Start Dt]+iterno()-1) <= [Proj End Dt] ;
Here is monthly budget loading stript. This one is not working because the while statement is not right.
LOAD [Proj ID] as GoalProj,
Year([Proj Start Dt]+iterno()-1) as GoalYear,
Month([Proj Start Dt]+iterno()-1) as GoalMonth,
num(Month([Proj Start Dt]+iterno()-1)) as GoalMonthNo,
//Date([Proj Start Dt]+iterno()-1) as GoalDate,
[Proj Tot Value]/((year([Proj End Dt])*12+Month([Proj End Dt]))-(Year([Proj Start Dt])*12+month([Proj Start Dt]))+1 ) as MonthlyGoalAmount
while (num(Year([Proj Start Dt])*12+month([Proj Start Dt]))+iterno()-1) <= num(year([Proj End Dt])*12+Month([Proj End Dt])) ;
Can you help find why this monthly loading script is not working?
Thank you guys very much!
Best regards!
Like above mentioned I would quite probably go with a daily budget-value and aggregate the needed periods over them within the UI. I didn't test a while-loop to create monthly budget-values but I created two versions of them with aggregation-loads. Maybe it's helpful for you to find the most suitable approach for your use-case:
DailyBudget:
load *, MonthName(monthstart(Date)) as MonthName;
load
*, date(From + iterno() -1) as Date, [Proj Tot Value] / (To - From) as DailyBudget
while From + iterno() - 1 <= To;
load
[Project ID], [Proj Name], [Proj Tot Value], [MonthlyGoalAmount], [Proj Start Dt], [Proj End Dt],
date(date#([Proj Start Dt], 'MM/DD/YYYY')) as From, date(date#([Proj End Dt], 'MM/DD/YYYY')) as To
inline [
Project ID Proj Name Proj Start Dt Proj End Dt Proj Tot Value MonthlyGoalAmount GoalMonth GoalYear
BJ1-0001 Work TRANSER 12/12/2016 09/30/2017 2612383 261238.3 Dec 2016
BJ1-0002 Work TRANSER 12/12/2016 09/30/2017 221581 22158.1 Dec 2016
BJ1-0003 Work TRANSER 12/12/2016 12/31/2017 1208 92.92307692 Dec 2016
BJ1-0004 Work TRANSER 12/12/2016 12/31/2017 206204 15861.84615 Dec 2016
BJ1-0005 Work TRANSER 12/12/2016 12/31/2017 729686 56129.69231 Dec 2016
BJ1-0006 Work TRANSER 12/12/2016 12/31/2017 39541 3041.615385 Dec 2016
BJ1-0007 Work TRANSER 12/12/2016 12/31/2017 5031 387 Dec 2016
BJ1-0008 Work TRANSER 12/12/2016 12/31/2017 40009 3077.615385 Dec 2016
BJ1-0009 Work TRANSER 12/12/2016 12/31/2017 6699 515.3076923 Dec 2016
BJ1-0010 Work TRANSER 12/12/2016 12/31/2017 3256 250.4615385 Dec 2016
BJ1-0011 Work TRANSER 12/12/2016 12/31/2017 73066 5620.461538 Dec 2016
BJ1-0012 Work TRANSER 12/12/2016 12/31/2017 12291 945.4615385 Dec 2016
BJ1-0013 Work TRANSER 12/12/2016 12/31/2017 197628 15202.15385 Dec 2016
BJ1-0014 Work TRANSER 12/12/2016 12/31/2017 186244 14326.46154 Dec 2016
BJ1-0015 Work TRANSER 12/12/2016 12/31/2017 157816 12139.69231 Dec 2016
BJ1-0016 Work TRANSER 12/12/2016 12/31/2017 64216 4939.692308 Dec 2016
BJ1-0017 Work TRANSER 12/12/2016 12/31/2017 51059 3927.615385 Dec 2016
] (txt, delimiter is \t);
MonthlyBudget1:
load [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date)))) as YearMonth1, sum(DailyBudget) as MonthlyBudget1
resident DailyBudget group by [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date))));
left join (MonthlyBudget1)
load [Project ID], avg([Proj Tot Value]) / count(distinct MonthName) as MonthlyBudget2
resident DailyBudget group by [Project ID];
- Marcus
I think the while-condition should be look something like this:
while addmonths(monthstart([Proj Start Dt]), iterno() - 1)) < monthend([Proj End Dt]);
maybe with a bit adjusting to really needed dates which might be the exact date or any kind of monthstart/monthend.
Beside them I'm not sure if I would use such a monthly budget-table - why not using the table on the daily-level and then aggregating them on any needed period within the charts?
- Marcus
Thank you very much - Marcus.
Your solution looks very very smart. But It looks only run one time when I test this script. The result is like this:
Project ID | Proj Name | Proj Start Dt | Proj End Dt | Proj Tot Value | MonthlyGoalAmount | GoalMonth | GoalYear |
BJ1-0001 | Work TRANSER | 12/12/2016 | 09/30/2017 | 2612383 | 261238.3 | Dec | 2016 |
BJ1-0002 | Work TRANSER | 12/12/2016 | 09/30/2017 | 221581 | 22158.1 | Dec | 2016 |
BJ1-0003 | Work TRANSER | 12/12/2016 | 12/31/2017 | 1208 | 92.92307692 | Dec | 2016 |
BJ1-0004 | Work TRANSER | 12/12/2016 | 12/31/2017 | 206204 | 15861.84615 | Dec | 2016 |
BJ1-0005 | Work TRANSER | 12/12/2016 | 12/31/2017 | 729686 | 56129.69231 | Dec | 2016 |
BJ1-0006 | Work TRANSER | 12/12/2016 | 12/31/2017 | 39541 | 3041.615385 | Dec | 2016 |
BJ1-0007 | Work TRANSER | 12/12/2016 | 12/31/2017 | 5031 | 387 | Dec | 2016 |
BJ1-0008 | Work TRANSER | 12/12/2016 | 12/31/2017 | 40009 | 3077.615385 | Dec | 2016 |
BJ1-0009 | Work TRANSER | 12/12/2016 | 12/31/2017 | 6699 | 515.3076923 | Dec | 2016 |
BJ1-0010 | Work TRANSER | 12/12/2016 | 12/31/2017 | 3256 | 250.4615385 | Dec | 2016 |
BJ1-0011 | Work TRANSER | 12/12/2016 | 12/31/2017 | 73066 | 5620.461538 | Dec | 2016 |
BJ1-0012 | Work TRANSER | 12/12/2016 | 12/31/2017 | 12291 | 945.4615385 | Dec | 2016 |
BJ1-0013 | Work TRANSER | 12/12/2016 | 12/31/2017 | 197628 | 15202.15385 | Dec | 2016 |
BJ1-0014 | Work TRANSER | 12/12/2016 | 12/31/2017 | 186244 | 14326.46154 | Dec | 2016 |
BJ1-0015 | Work TRANSER | 12/12/2016 | 12/31/2017 | 157816 | 12139.69231 | Dec | 2016 |
BJ1-0016 | Work TRANSER | 12/12/2016 | 12/31/2017 | 64216 | 4939.692308 | Dec | 2016 |
BJ1-0017 | Work TRANSER | 12/12/2016 | 12/31/2017 | 51059 | 3927.615385 | Dec | 2016 |
The reason I want to generate monthly goal is my revenue actual is by month.
Thank you very much for your inputs. Appreciate.
Best regards.
Monthly data could be treated as daily data just by converting the Year and Month into a date. For example with something like: makedate(Year, Month, 1) which made the handling quite easy. We use this approach since many years and concatenate the budget data just to the sales data whereby the comparing of both values is done with a monthly forecast of sales and the budget is already a monthly value.
- Marcus
Marcus,
You are awesome with so many good solutions on my question. We have monthly revenue actual. But for project budget is by project period based on project start date and project end date. So I try to average this budget by months and compare to actual.
For the loading script, the daily script is working and can get budget for each day. I just cannot understand why the monthly script is only run once when load.
Thank you again - Marcus. Your reply is greatly appreciated!
Like above mentioned I would quite probably go with a daily budget-value and aggregate the needed periods over them within the UI. I didn't test a while-loop to create monthly budget-values but I created two versions of them with aggregation-loads. Maybe it's helpful for you to find the most suitable approach for your use-case:
DailyBudget:
load *, MonthName(monthstart(Date)) as MonthName;
load
*, date(From + iterno() -1) as Date, [Proj Tot Value] / (To - From) as DailyBudget
while From + iterno() - 1 <= To;
load
[Project ID], [Proj Name], [Proj Tot Value], [MonthlyGoalAmount], [Proj Start Dt], [Proj End Dt],
date(date#([Proj Start Dt], 'MM/DD/YYYY')) as From, date(date#([Proj End Dt], 'MM/DD/YYYY')) as To
inline [
Project ID Proj Name Proj Start Dt Proj End Dt Proj Tot Value MonthlyGoalAmount GoalMonth GoalYear
BJ1-0001 Work TRANSER 12/12/2016 09/30/2017 2612383 261238.3 Dec 2016
BJ1-0002 Work TRANSER 12/12/2016 09/30/2017 221581 22158.1 Dec 2016
BJ1-0003 Work TRANSER 12/12/2016 12/31/2017 1208 92.92307692 Dec 2016
BJ1-0004 Work TRANSER 12/12/2016 12/31/2017 206204 15861.84615 Dec 2016
BJ1-0005 Work TRANSER 12/12/2016 12/31/2017 729686 56129.69231 Dec 2016
BJ1-0006 Work TRANSER 12/12/2016 12/31/2017 39541 3041.615385 Dec 2016
BJ1-0007 Work TRANSER 12/12/2016 12/31/2017 5031 387 Dec 2016
BJ1-0008 Work TRANSER 12/12/2016 12/31/2017 40009 3077.615385 Dec 2016
BJ1-0009 Work TRANSER 12/12/2016 12/31/2017 6699 515.3076923 Dec 2016
BJ1-0010 Work TRANSER 12/12/2016 12/31/2017 3256 250.4615385 Dec 2016
BJ1-0011 Work TRANSER 12/12/2016 12/31/2017 73066 5620.461538 Dec 2016
BJ1-0012 Work TRANSER 12/12/2016 12/31/2017 12291 945.4615385 Dec 2016
BJ1-0013 Work TRANSER 12/12/2016 12/31/2017 197628 15202.15385 Dec 2016
BJ1-0014 Work TRANSER 12/12/2016 12/31/2017 186244 14326.46154 Dec 2016
BJ1-0015 Work TRANSER 12/12/2016 12/31/2017 157816 12139.69231 Dec 2016
BJ1-0016 Work TRANSER 12/12/2016 12/31/2017 64216 4939.692308 Dec 2016
BJ1-0017 Work TRANSER 12/12/2016 12/31/2017 51059 3927.615385 Dec 2016
] (txt, delimiter is \t);
MonthlyBudget1:
load [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date)))) as YearMonth1, sum(DailyBudget) as MonthlyBudget1
resident DailyBudget group by [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date))));
left join (MonthlyBudget1)
load [Project ID], avg([Proj Tot Value]) / count(distinct MonthName) as MonthlyBudget2
resident DailyBudget group by [Project ID];
- Marcus
Dear Marcus,
Your solution looks great for my question. MonthlyBudget2 is looks right. I am going to implement your script to my app.
Thank you very much on your smart and nice solution. Wish you all the best.
Dust