Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following script. While I included all of it the only thing I really want to accomplish is write this script once and loop through it for every month from JAN2010 through OCT2013. I have noted the variables (in red) and the table name I have created but would need to change each time I loop through. Currently I create this same script 40+ times to get it for each month through 2013!!
For instance on the second loop though JAN2010WHERE would become FEB2010WHERE, JAN2010RETENSION would become FEB2010RENTENSION, and the table name Line_of_BusinessCube_Jan2010 would become Line_of_Business_Feb2010 and so on for each successive month.
ProductionByLine_of_BusinessByCust:LoadLine_of_Business as [Line_of_Business Cube Pre],sum([Commission]) as [Commission Line_of_Business Cube Pre] Resident Production
Where
$(JAN2010WHERE)
Group By [Line_of_Business]
Order by Line_of_Business ASC;
left Join(ProductionByLine_of_BusinessByCust)LoadLine_of_Business as [Line_of_Business Cube Pre],Customer_Name as [Cust Cube Pre],FirstValue(ID|Customer) As ID|Customer,sum([Commission]) as [Commission Line_of_BusinessCust Cube Pre] Resident Production
Where
$(JAN2010WHERE)Group By [Line_of_Business],Customer_NameOrder By Line_of_Business ASC;
Line_of_BusinessCube_Jan2010:
Load [Line_of_Business Cube Pre]&'|'&[Cust Cube Pre]&'|'& $(JAN2010RETENSION) as Key|RetentionLine_of_Business,[Line_of_Business Cube Pre],ID|Customer,MakeDate(2010,01,01)as [Retention Date],[Commission Line_of_BusinessCust Cube Pre],[Commission Line_of_Business Cube Pre],if(previous([Line_of_Business Cube Pre])=[Line_of_Business Cube Pre],rangesum([Commission Line_of_BusinessCust Cube Pre],peek([CumCommission Line_of_Business Cube])),[Commission Line_of_BusinessCust Cube Pre]) as [CumCommission Line_of_Business Cube],if(previous([Line_of_Business Cube Pre])=[Line_of_Business Cube Pre],rangesum([Commission Line_of_BusinessCust Cube Pre],peek([CumCommission Line_of_Business Cube])),[Commission Line_of_BusinessCust Cube Pre])/[Commission Line_of_Business Cube Pre] as [Commission Line_of_Business CummPerc Cube]Resident ProductionByLine_of_BusinessByCustOrder by [Line_of_Business Cube Pre] asc, [Commission Line_of_BusinessCust Cube Pre] desc;
Drop Table ProductionByLine_of_BusinessByCust;