Hi all,
There is one scenario in frontof me in which I need the status on the basis of amount for every agent. Forex: there is one agent number = ‘00000006’ so my requirement is that:
- The joining date ofthis agent is 19th march 2012 and accounting date (i.e Post_Date) is1st march 2012 because this agent was joined in last financial yearand accounting entry is updating for each month so for april month he does thebusiness of 54,336.
- As per the businessfor every new agent they give the grace period of starting 1 month they do notcount his first month’s business even if he does not do any business. Thereforethe joining month for every agent is next to the actual joining month. In thiscase the joining month of agent ‘00000006’ is April 2012. We count his businessfrom this month.
- I have to fix thestatus as ‘inactive’ or ‘active’ on the basis of amount for each agent. Rightnow for MTD calculations if amount of any agent is greater the 15,000 then heis active otherwise inactive. For ex: Right now I am in April but my agentjoined in March so for march he is inactive by default because we do not counthis business from first month but in April if he complete with his target thenhe is active agent.
- In Case of YTDcalculations suppose right now I am in April but my agent joined in March so Ihave to count his business from march month i.e suppose he has done thebusiness of 5,000 in march and 18,000 in April so in my YTD calculations hisstatus is active because his total is 33,000 which is greater then 15,000. Herethere is one more scenario i.e in case if he would not do any business or lessbusiness in May suppose of 5,000 then he will be inactive for my May monthcalculations because in YTD calculations every month target is increasing by15,000. So for 1st month target is 15,000, for 2nd monthtarget is 30,000, for 3rd month target is 45,000, etc.
I am attaching XL hereby foryour reference. Please have a look on this and reply asap. Let me know in caseof any clarification.
