1 Reply Latest reply: Nov 9, 2018 3:55 PM by vamsee duggirala RSS

    How to divide Total  revenue between two months Section

    Tahreen Shaikh

      Hello All,

       

      I have been assigned a task where in have to limit only for Bengaluru and subtotal is Revenue.

       

      But the task here is I need to Divide the total revenue as per the days in a Month for InvoiceFromDate  to InvoiceToDate.


      An example is attached in the output Please refer onlt the task for bengaluru.

       

       

      Bang_Invoice:

      LOAD BusinessId,

           BusinessName as Bang_Invoice,

           Date(InvoiceFromDate, 'MMM-YY') as Month_Yr,

           Date(InvoiceToDate, 'MMM-YY') as Month_yr,

           Id as InvoiceId,

           CreatedOn

      FROM

      [C:\Users\tahre\Desktop\GI - Task\Task 2\Invoice.xls]

      (biff, embedded labels, table is Sheet1$)

      where (BusinessName='Bengaluru');

       

       

      LOAD InvoiceId,

           SubTotal as Revenue

      FROM

      [C:\Users\tahre\Desktop\GI - Task\Task 2\Invoice_Line.xls]

      (biff, embedded labels, table is Sheet1$);

       

       

       

       

       

       

      Exit Script;

        • Re: How to divide Total  revenue between two months Section
          vamsee duggirala

          Hi Tahreen,

           

          Try

           

          SubTotal_Map:
          Mapping LOAD

          InvoiceId,
          SUM(SubTotal) as Revenue
          FROM
          [Invoice_Line.xls]
          (
          biff, embedded labels, table is Sheet1$)
          Group by InvoiceId;
          Bang_Invoice:
          LOAD
          BusinessId,
          BusinessName as Bang_Invoice,
          Date(Floor(NUM(InvoiceFromDate))) as From_Date,
          Date(Floor(NUM(InvoiceToDate))) as To_Date,
          Interval( Date(Floor(NUM(InvoiceToDate)))- Date(Floor(NUM(InvoiceFromDate))), 'dd' ) as Number_Of_Days,
          ApplyMap('SubTotal_Map',Id ,0) as SubTotal,
          Id as InvoiceId,
          Date(Floor(NUM(CreatedOn))) as CreatedOn
          FROM
          [Invoice.xls]
          (
          biff, embedded labels, table is Sheet1$)
          where (BusinessName='Bengaluru');



          Bang_Invoice_Step1:
          NoConcatenate
          Load
          *,
          Month( From_Date + IterNo()-1 )&'-'& Year( From_Date + IterNo()-1 ) as Month_Yr
          Resident Bang_Invoice
          While IterNo() <= To_Date - From_Date ;

          DROP Table Bang_Invoice;

          Invoice:
          LOAD
          Bang_Invoice,
          InvoiceId,  /** Remove this if you need only at month yr level **/
          Month_Yr,
          SUM(SubTotal/Number_Of_Days) as Daily_Total
          Resident Bang_Invoice_Step1
          Group by
          Bang_Invoice,
          InvoiceId,,  /** Remove this if you need only at month yr level **/
          Month_Yr
          ;
          DROP Table Bang_Invoice_Step1;