Announcements
cancel
Showing results for
Did you mean:
Creator II

## Convert monthly data by location and year to daily

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!

1 Solution

Accepted Solutions
Luminary Alumni

Hi,

Try this

Data:

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;

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]);

4 Replies
Luminary Alumni

Hi,

Try this

Data:

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;

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]);

Creator II
Author

Your solution was perfect!  Thank You!!!!

MVP

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

Creator II
Author

Thanks for the catch Tresesco!

Community Browser