4 Replies Latest reply: Feb 23, 2016 12:23 AM by gidon david RSS

    Spead  income by dates in month

    gidon david

      Hi guys

      I need help , enclosed is an excel file

      the input is

      invoice , amount , fromdate to date

       

      I need to spead the amount by months , while take in consideration

      number of days in a month , which calculated as 30 days per month

       

      thanks for your help

       

      gidon

        • Re: Spead  income by dates in month
          Sunny Talwar

          I am sure there mught be a better way to do this, but I was able to go this far:

           

          Table:

          LOAD Invoice,

            Amount,

            FromDate,

            ToDate,

            Date(AddMonths(MonthEnd(FromDate), (IterNo() -1)), 'MMM-YYYY') as Date,

            If(IterNo() = 1, 30 - Day(FromDate),

            If(Floor(AddMonths(MonthEnd(FromDate), (IterNo() -1))) = Floor(MonthEnd(ToDate)),

            If(Match(Num(Month(ToDate)), 1, 3, 5, 7, 8, 10, 12) and Match(Day(ToDate), 31) or

            Match(Num(Month(ToDate)), 2) and Day(ToDate)>= 28, 30, Day(ToDate)), 30)) as Days

          While AddMonths(MonthEnd(FromDate), (IterNo() -1)) <= MonthEnd(ToDate);

          LOAD * Inline [

          Invoice, Amount, FromDate, ToDate

          100, 1500, 10/11/2015, 6/2/2016

          101, 1000, 1/12/2015, 31/01/2016

          102, 2700, 27/12/2015, 2/2/2016

          ];

           

          Join(Table)

          LOAD Invoice,

            Sum(Days) as Sum

          Resident Table

          Group By Invoice;

           

          FinalTable:

          LOAD Invoice,

            Date,

            (Amount * Days)/Sum as AmountPerMonth

          Resident Table;

           

          DROP Table Table;


          Capture.PNG


          The complication I faced was around getting 30 days for each month instead of the actual number of days. Somebody else might have a better solution

          • Re: Spead  income by dates in month
            Massimo Grossi

            1.png

             

            V:

            LOAD

              *,

              (daysfirstmonth/30 + monthdiff + daylastmonth/30) as period;

            LOAD

              *,

              year(todate)*12 + month(todate) - (Year(fromdate)*12 + Month(fromdate))-1 as monthdiff,

              rangemin(30 - day(fromdate), 30)  as daysfirstmonth,

              rangemin(day(todate) - 1 +1,30)  as daylastmonth

              ;

            LOAD

              //MonthStart(todate) as end,

              //Date(floor(MonthEnd(fromdate))+1) as start, 

              Invoice,

                amount,

                fromdate,

                todate

            FROM

            [Spead Income.xlsx]

            (ooxml, embedded labels, table is Sheet1)

            Where Len(Trim(Invoice))>0

            ;

             

            Left Join (V)

            load

              fromdate,

              date(AddMonths(MonthStart(fromdate), IterNo()-1), 'MMM-YY') as month

            Resident V

            While AddMonths(MonthStart(fromdate), IterNo()-1) <= MonthStart(todate);

             

            Left Join (V) LOAD

              fromdate, month, 1 as IsFirst

            Resident V

            Where Peek('fromdate') <> fromdate

            order by fromdate, month;

            Left Join (V) LOAD  fromdate, month, 1 as IsLast

            Resident V

            Where Peek('fromdate') <> fromdate

            order by fromdate, month desc;