Hello Ram,
We can achieve the same using below sample script:
SalesData:
LOAD DATE#(Date, 'MM/DD/YYYY') AS Date, DATE(DATE#(Date, 'MM/DD/YYYY'), 'MMM YYYY') AS Month, SalesRep, Sales;
LOAD * INLINE [
Date, SalesRep, Sales
01/01/2017, Arun, 12000
01/01/2017, Sam, 11000
01/01/2017, Vinoth, 10000
02/01/2017, Arun, 12000
02/01/2017, Sam, 17000
02/01/2017, Vinoth, 11000
03/01/2017, Arun, 21000
03/01/2017, Sam, 11000
03/01/2017, Vinoth, 11200
04/01/2017, Arun, 22000
04/01/2017, Sam, 31000
04/01/2017, Vinoth, 40000
];
//Get the Total Sales for every month
INNER JOIN
Data:
LOAD Month
,Sum(Sales) AS MonthlySales
Resident SalesData
Group By Month;
//Get the Maximum Sales for every month
INNER JOIN
Data:
LOAD Month
,Max(Sales) AS Sales
Resident SalesData
Group By Month;
Also refer the attached sample application.
Hope this will help.
Regards!
Rahul
