@PaigeMiller First the use of 27 months is required in our public health practice and epidemiological view point. The row 2 can be ignored due to prior data (<2018) not available to calculate mean, though we could calculate mean for rows 3 and 4, but I wanted to start at row 5, where I highlighted in green. Data for row 13 (i.e. when we reach Feb 2024) will be available by adding the year 2023 (5 year block from 2019-2023), and Feb 2024 becomes the current month to compare. This is called rolling average. The program I am writing will be automated runs once a month off the database, so it will sends out alert when current month counts is higher than expected.
@FreelanceReinh I did run your script. It did produce a desired result for now. I am not sure if I will have any issue going into next year, i.e. 2024. The 5-years block will be from 2019-2023, and current year will be 2024. Regarding missing values, I will insert missing month and missing values as 'zero'. For example for disease A, no data in July, I will insert 0 for that month. Technically zero is a value (i.e. zero cases reported). I am leaning towards accepting as solution in couple of days after few checks.
... View more
