1 Reply Latest reply: Sep 19, 2013 7:52 AM by Jean-Pierre Bakhache RSS

    Distributing month amount on days

      Hi!

       

      I have a budget file (excel) that has a budget amount for each month in a year. What I want to do is to distribute this amount to every day of the month. I guess I need to create some sort of loop do distribute the amount.

       

      First I need to calculate the number of days in each month to know how many times i need to divide my month amount, which really isn't that hard.

       

      But how do I create the loop to distribute the amount? Do I need 2 loops? One for every month and one for each day of the month?

       

      Appreciate any help or suggestions!

        • Re: Distributing month amount on days
          Jean-Pierre Bakhache

          Hi Kim,

           

          You can try the below and adjust it based on your data model:

           

          let vmindate = num(MakeDate(2012,12,1));
          let vmaxdate = num(MakeDate(2013,4,30));

          TableDates:
          LOAD $(vmindate)+RowNo()-1 as PeriodDate,
          Day(MonthEnd($(vmindate)+RowNo()-1)) as NumberOfDays,
          Year($(vmindate)+RowNo()-1) as Year,
          num(Month($(vmindate)+RowNo()-1)) as Month
          AutoGenerate $(vmaxdate)-$(vmindate)+1;

          Inner Join(TableDates)
          LOAD * Inline [
          Year, Month, Budget
          2012, 12, 14000
          2013, 1, 10000
          2013, 2, 11000
          2013, 3, 9500
          2013, 4, 15000
          ]
          ;

          DailyBudget:
          LOAD *,
          Budget/NumberOfDays as DailyBudget
          Resident TableDates;
          DROP Table TableDates;

          Hope this helps.