Convert monthly Budget to Daily Budget ?

gauravgg — Tue, 04 Apr 2017 15:37:29 GMT

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:

load * inline

[

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:

load * inline

[

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

thanks in advance

Re: Convert monthly Budget to Daily Budget ?

aarkay29 — Tue, 04 Apr 2017 16:23:36 GMT

Something Like this

Budget:

load * inline [

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:

Load

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

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

Load

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:

LOAD

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

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

AUTOGENERATE

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

Calendar:

Load

Num,

TempDate as Date,

Year(TempDate) as Year,

Month(TempDate) as Month

Resident

TempCalendar;

Drop Table TempCalendar;

Left join (Calendar)

Load

Plant,

Year,

Sum(Value)/365 as Daily_Budget

Resident

Budget

Group By

Plant,

Year;

;

Drop Table Budget;

topic Re: Convert monthly Budget to Daily Budget ? in App Development

Convert monthly Budget to Daily Budget ?

Re: Convert monthly Budget to Daily Budget ?