Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a budget table by month by location going back 3 years...( below & attached sample data) .
DPT_ID | MONTH_DATE | LOCATION | BUDGETED | DATE_YEAR | DATE_MONTH |
1 | 1/1/2012 | 21 | 555 | 2012 | 1 |
2 | 2/1/2012 | 21 | 856 | 2012 | 2 |
3 | 3/1/2012 | 21 | 678 | 2012 | 3 |
4 | 4/1/2012 | 21 | 425 | 2012 | 4 |
5 | 5/1/2012 | 21 | 786 | 2012 | 5 |
6 | 6/1/2012 | 21 | 786 | 2012 | 6 |
7 | 7/1/2012 | 21 | 422 | 2012 | 7 |
8 | 8/1/2012 | 21 | 786 | 2012 | 8 |
9 | 9/1/2012 | 21 | 435 | 2012 | 9 |
10 | 10/1/2012 | 21 | 453 | 2012 | 10 |
11 | 11/1/2012 | 21 | 963 | 2012 | 11 |
12 | 12/1/2012 | 21 | 424 | 2012 | 12 |
I need to recreate the exact same table as above but convert the monthly budget to daily for each month by location. so for example, I would take Jan 555 / 31 = 17.90 and then generate 31 dates with 17.9 for daily budget ....Feb 856/29 = 29.5 for 29 days... till the end of the year. Then loop for the next location / Year and monthly budget to convert to daily.
Any idea how I would go about doing this in the script?
Thanks for the help!
Hi,
Try this
Data:
LOAD
DPT_ID,
MONTH_DATE,
LOCATION,
BUDGETED,
DATE_YEAR,
DATE_MONTH,
BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,
Date(MONTH_DATE + IterNo() - 1) AS DATE
WHILE Num(MonthStart + IterNo() - 1) <= MonthEnd;
LOAD DPT_ID,
Date(MONTH_DATE) AS MONTH_DATE,
LOCATION,
BUDGETED,
DATE_YEAR,
DATE_MONTH,
Num(MonthEnd(Date(MONTH_DATE))) AS MonthEnd,
Num(MonthStart(Date(MONTH_DATE))) AS MonthStart
FROM
(ooxml, embedded labels, table is [ Sample]);
Hi,
Try this
Data:
LOAD
DPT_ID,
MONTH_DATE,
LOCATION,
BUDGETED,
DATE_YEAR,
DATE_MONTH,
BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,
Date(MONTH_DATE + IterNo() - 1) AS DATE
WHILE Num(MonthStart + IterNo() - 1) <= MonthEnd;
LOAD DPT_ID,
Date(MONTH_DATE) AS MONTH_DATE,
LOCATION,
BUDGETED,
DATE_YEAR,
DATE_MONTH,
Num(MonthEnd(Date(MONTH_DATE))) AS MonthEnd,
Num(MonthStart(Date(MONTH_DATE))) AS MonthStart
FROM
(ooxml, embedded labels, table is [ Sample]);
Your solution was perfect! Thank You!!!!
Nice solution Jagan!
however, a small but significant bug is there in this solution.
BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,
MonthEnd would have last millisecond of the last date of the month which would result a difference (MonthEnd-MonthStart) by extra 1 (0.99..). That is for ex. - the January value will be divided by 32 (31.99...) instead of 31. You can handle this by two alternate ways:
BUDGETED/ (Floor(MonthEnd) - MonthStart + 1) AS Budget_Daily
Or,
BUDGETED/ Day(MonthEnd) AS Budget_Daily
Thanks for the catch Tresesco!