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

    Convert monthly Budget to Daily Budget ?

    gaurav gopale

      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 ?
          Aar Kay

          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;