* Data explainations: Event date basically specifies when a task was completed or trigger is incurred (for Bursts etc.), train refers to the amount trained during that day (under event amount) and for what sport activity. When Event Type is 'Burst', it refers to the amount of time needed to be trained by the athlete overall within 7 days of the event being triggered. 'Burst' does not refer to amount of time trained, its only a trigger mechanism * Goals: To create a total hours trained variable and also to flag if athlete completed aimed training length for time period My calculations should be like: * Burst amounts shouldn’t be included * Has athlete met Burst target? *Amount of time trained in that sports discipline after burst trigger Last day, which summation should count towards meeting target Player Name Event Date Event Type Event Name Event Amount Total Amount Trained Target Flag Post Burst Training Burst End Date John Smith 12/03/2012 Train Soccer 5 John Smith 17/03/2012 Train Soccer 4.5 John Smith 21/03/2012 Burst Soccer 3 9.5 NO 28/03/2012 Mary Weed 1/03/2012 Burst Tennis 2 0 8/03/2012 Mary Weed 7/03/2012 Train Tennis 4 4 YES James Wood 2/03/2012 Train Cross Country 3 James Wood 11/03/2012 Train Cross Country 3 James Wood 13/03/2012 Burst Cross Country 5 20/03/2012 James Wood 16/03/2012 Train Cross Country 3 James Wood 16/03/2012 Train Cross Country 3 12 YES 6 James Wood 19/03/2012 Burst Swimming 10 26/03/2012 James Wood 20/03/2012 Train Swimming 3 James Wood 22/03/2012 Train Swimming 2 Peter Mach 7/03/2012 Train Soccer 5 10 YES 10 George Poll 17/03/2012 Burst Ice Hockey 8 24/03/2012 George Poll 20/03/2012 Train Ice Hockey 12 12 YES 12 Ideal results should be like: * Data should be finalized in something like this form Player Name Total Amount Trained Target Flag John Smith 9.5 NO Mary Weed 4 YES James Wood 22 YES Peter Mach 5 YES George Poll 12 YES Hopefully this makes sense?
... View more