How to use MTD and YTD in load statement

[laxmanqlik]

hi everyone,

i have to calculate the revenue of an organisation n term of YearToDate(YTD) and MonthToDate(MTD)

can anyone let me know the way how to calculate those by scripting within the Load.

thanks in advance

[rahulpawarb]

Hello Laxman,

Please refer below sample script:

Orders:
LOAD [Row ID], 
     [Order ID], 
     [Order Date], 
     [Ship Date], 
     [Ship Mode], 
     [Customer ID], 
     [Customer Name], 
     Segment, 
     City, 
     State, 
     Country, 
     [Postal Code], 
     Market, 
     Region, 
     [Product ID], 
     Category, 
     [Sub-Category], 
     [Product Name], 
     Sales, 
     Quantity, 
     Discount, 
     Profit, 
     [Shipping Cost], 
     [Order Priority]
FROM
[Global Superstore.xls]
(biff, embedded labels, table is Orders$);




MTDOrders:
LOAD
     Year([Order Date]) AS Year,
     Month([Order Date]) AS Month, 
     Sum(Sales) AS MTD_Sales, 
     Sum(Quantity) AS MTD_Quantity, 
     Sum(Discount) AS MTD_Discount, 
     Sum(Profit) AS MTD_Profit, 
     Sum([Shipping Cost]) AS MTD_ShippingCost
Resident Orders
Group By      Year([Order Date]), Month([Order Date]);

Hope this will be helpful.

Regards!

Rahul