Folks,
I have an issue in calculating a YTD result on data which is a count.
To explain properly currently I have a set of data stored in excel which records the date and other details of vehicle incidents. I also have a set of data which records mileage run by day and service.
For each period/month, I do a count on all the vehicle incidents which are classified as RTAs, road traffic accidents, this number is then divided by the mileage operated in the month, and then this is expressed as a number of RTAs per 100,000km.
At present this works fine, but the only way to show the YTD position is to highlight all the periods/months or by leaving all periods/months unselected. But what I want the user to do is to be able to pick a period say 5/May and for the month value to be displayed as well as the YTD value.
Any thoughts of the best method to do this?? My current formula is as below
=IF([Incident Category]='RTA',((Count (DISTINCT [V.I.R. Number]) / (((sum([Scheduled Miles Value])+sum([Empty Miles Value])+sum([Absence & Lateness Value])+sum([Sickness Value])+sum([No Serviceable Bus Value])+sum([Mechanical Value])+sum([Other Deductable Value])+sum([Traffic Value])+sum([Lost Empty Value])+sum([Other Non Deductable Value])+sum([Unscheduled Live Value])+sum([Unscheduled Dead Value]))*1.609344)/100000)))
I have a flag calendar in place using th formula below give me the YTD days when a period is selected. But I am unsure hot to combine the two.
=
sum({$<[Met Pd]={">=$(#v1Mth) <=$(#v2Mth)"}>} CurYTDFlag)
