I need to calculate some bonus payments which are based on a rolling 'year to date' sales figure.
I am recording for each customer advisor individual commissions they recieve and the for each commission there is a commission date. I can therefore identify which commissions fall within a given month.
I am having problems trying to determine the best way to query this. Ideally, when reporting, I would like to be prompted to enter a month & year (eg July 2002) and the commissions for each customer advisor are totalled for the previous 12 months (eg Aug 2001 to July 2002) and the bonus (a percentage) calculted. The bonuses would then be populated into a bonus history table for each customer advisor.
I can get a query to work by entering 'from' & 'to' dates' but I would like it to be easier for users ie enter a month & year & the calculation are done. Has anyone got any suggestions for expressions which can calculate this year to date figure? Thanks in advance for your help.
I am recording for each customer advisor individual commissions they recieve and the for each commission there is a commission date. I can therefore identify which commissions fall within a given month.
I am having problems trying to determine the best way to query this. Ideally, when reporting, I would like to be prompted to enter a month & year (eg July 2002) and the commissions for each customer advisor are totalled for the previous 12 months (eg Aug 2001 to July 2002) and the bonus (a percentage) calculted. The bonuses would then be populated into a bonus history table for each customer advisor.
I can get a query to work by entering 'from' & 'to' dates' but I would like it to be easier for users ie enter a month & year & the calculation are done. Has anyone got any suggestions for expressions which can calculate this year to date figure? Thanks in advance for your help.
