How to divide Total revenue between two months Section

[tahreen371]

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

(biff, embedded labels, table is Sheet1$)

where (BusinessName='Bengaluru');

LOAD InvoiceId,

     SubTotal as Revenue

FROM

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

Exit Script;

[vamsee]

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;