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

# How to divide Total  revenue between two months Section

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:

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\$)

SubTotal as Revenue

FROM

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

Exit Script;

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

Hi Tahreen,

Try

SubTotal_Map:

InvoiceId,
SUM(SubTotal) as Revenue
FROM
[Invoice_Line.xls]
(
biff, embedded labels, table is Sheet1\$)
Group by InvoiceId;
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\$)

Bang_Invoice_Step1:
NoConcatenate
*,
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: