Please help me in calculation pro rata data for each month.
We are maintaining a flag for pro rata. We need to distribute the measure (Openings in attached excel) equally for each month from Month(today()) to Pro rata month.
I have given detail source data and output required in attached excel.
Veman Reddy wrote:Thanks for your support but I need the exact situation. I am unable to implemnt this.
OK, see attached for a solution for your exact situation then. Script below:
SET DateFormat='MMM-YY';
Source: LOAD Loc ,date(date#(Date,'MMM-YY')) as Date ,"Pro Rata" ,Openings INLINE [ Loc,Date,Pro Rata,Openings A,Sep-10,0,10 A,Oct-10,0,8 A,Nov-10,1,12 A,Dec-10,1,28 B,Sep-10,0,6 B,Oct-10,1,24 B,Nov-10,1,33 B,Dec-10,0,9 ];
Temp: NOCONCATENATE LOAD Loc as Location ,Date as Month ,Openings as "Direct Openings" ,0 as "Prorated Openings" RESIDENT Source WHERE "Pro Rata"=0 ; INNER JOIN (Source) LOAD 1 as "Pro Rata" AUTOGENERATE 1 ; CONCATENATE (Temp) LOAD Loc as Location ,date(addmonths(Date,1-iterno())) as Month ,0 as "Direct Openings" ,Openings/(year(Date)-year(today())+month(Date)-month(today())+1) as "Prorated Openings" RESIDENT Source WHILE addmonths(Date,1-iterno()) >= monthstart(today()) ;
Final: NOCONCATENATE LOAD * ,"Direct Openings"+"Prorated Openings" as "Total Openings" ; LOAD Location ,Month ,sum("Direct Openings") as "Direct Openings" ,sum("Prorated Openings") as "Prorated Openings" RESIDENT Temp GROUP BY Location, Month ;
