1 Reply Latest reply: Apr 4, 2017 12:23 PM by Aar Kay

# Convert monthly Budget to Daily Budget ?

Hi All

I have the following budget file in excel ,which is monthly budget for Plants.(for example i have took for only one plant)

I have to convert it into daily budget .

Budget:

[

Plant,Month ,Value,Year

P1,Jan,100,2016

P1,Feb,100,2016

P1,Mar,100,2016

P1,Apr,100,2016

P1,May,100,2016

P1,Jun,100,2016

P1,Jul,100,2016

P1,Aug,100,2016

P1,Sep,100,2016

P1,Oct,100,2016

P1,Nov,100,2016

P1,Dec,100,2016

];

What i want to do is , for i want to sum(Value ) for P1 Plant and then divide it by 365 days i.e sum(Value)/365 group by Plant

sum(1200)/365 = 3.28

and also want date in that table.

So the final table should be as follows

FinalTable:

[

Plant,Month,Value,Year,  Date,   DailyBudget

P1, Jan,  100,  2016,  01/01/2016, 3.28

P1, Jan,  100,  2016,  02/01/2016, 3.28

P1, Jan,  100,  2016,  03/01/2016, 3.28

P1, Jan,  100,  2016,  04/01/2016, 3.28

P1, Jan,  100,  2016,  05/01/2016, 3.28

P1, Jan,  100,  2016,  06/01/2016, 3.28

P1, Jan,  100,  2016,  07/01/2016, 3.28

P1, Jan,   100,  2016,  08/01/2016, 3.28

.

.

.

.

till

.

.

P1, Jan, 100  ,2016,  31/12/2016,  3.28

];

In My excel data is for multipal Plants,here for exemple i have showed only for one plant .

Can anyone help me in this

• ###### Re: Convert monthly Budget to Daily Budget ?

Something Like this

Budget:

Plant,Month ,Value,Year

P2,Jan,200,2017

P2,Feb,200,2017

P2,Mar,200,2017

P2,Apr,200,2017

P2,May,200,2017

P2,Jun,200,2017

P2,Jul,200,2017

P2,Aug,200,2017

P2,Sep,200,2017

P2,Oct,200,2017

P2,Nov,200,2017

P2,Dec,200,2017

P1,Jan,100,2016

P1,Feb,100,2016

P1,Mar,100,2016

P1,Apr,100,2016

P1,May,100,2016

P1,Jun,100,2016

P1,Jul,100,2016

P1,Aug,100,2016

P1,Sep,100,2016

P1,Oct,100,2016

P1,Nov,100,2016

P1,Dec,100,2016

];

MaxDates:

(Makedate((MinYear),01,01)) as Min,

(Makedate((MaxYear),12,31)) as Max;

max(Year) as MaxYear,

min(Year) as MinYear

Resident

Budget;

Trace;

Let vStartDate = Num(Peek('Min',0,MaxDates));

Let vEndDate = Num(Peek('Max',0,MaxDates));

Drop Table MaxDates;

TempCalendar:

\$(vStartDate) + RowNo() - 1       AS Num,

date(\$(vStartDate) + RowNo() - 1) AS TempDate

AUTOGENERATE

\$(vEndDate) - \$(vStartDate) + 1;

Calendar:

Num,

TempDate as Date,

Year(TempDate) as Year,

Month(TempDate) as Month

Resident

TempCalendar;

Drop Table TempCalendar;

Left join (Calendar)

Plant,

Year,

Sum(Value)/365 as Daily_Budget

Resident

Budget

Group By

Plant,

Year;

;

Drop Table Budget;