how to count occurances of a number being larger than a previous number within a specific time period (rolling 3 months)

[Jordon Burn]

Hi Everyone,

this is my first post I have found some great solutions on this site over the last few months but I can find how to do what I am looking for and was hoping someone could help me

I run a spread sheet to track absence days at work and need help with how to count specific absence triggers the triggers are 3 periods of absence in 3 months or 5 periods of absence in 12 months I have a pivot which counts the days absent each week this sits in the background and I display the results in a different page

the current table has a long list of employees in the row labels it has week number in the column labels and count of days absent in the values.

this lets me see if anyone has had more than 10 days off in the last 12 months as this is the other trigger any idea how to count if a number is larger than 0 more than 3 times in 3 months when the week before was 0 indicating a separate absence and not continued absence over 2 weeks.

Hope this makes sense.

Thanks,
Jord

 

[TDCox]

Hi @Jordon Burn

There are many ways to solve a problem such as this, but it would be helpful to see how your data is laid out - does each employee repeat every week or are there multiple week columns for every employee? How long does the data go back e.g. is data over 12 months deleted or could an Employee's full work history be seen? Is the 12 months a fixed period (e.g. April to March) or a rolling 12 months? How often do you need to see this or does it need to be real time? In either event, how is the data updated?