Forum Discussion
Post Prodigylogic to look back 4 weeks
Hello,
I need to write a logic that is looking back if a store # is hard to staff either Y or N and then categorize it based on the amount of weeks it's been hard to staff. Example below - Store 25 would be "Hard to Staff< 4 weeks"; Store 38 would be "Hard to Staff >= 4 weeks" and Store 68 would be "Off of Hard to Staff for >= 4 weeks." Somehow so it would use the latest date in the column (in this case today, 11/13/23)
| Store Number | Week Of | Hard to Staff (HTS) |
| 25 | 10/2/2023 | Y |
| 25 | 10/9/2023 | Y |
| 25 | 10/16/2023 | Y |
| 25 | 10/23/2023 | |
| 25 | 10/30/2023 | |
| 25 | 11/6/2023 | Y |
| 25 | 11/13/2023 | Y |
| 38 | 10/2/2023 | Y |
| 38 | 10/9/2023 | Y |
| 38 | 10/16/2023 | Y |
| 38 | 10/23/2023 | Y |
| 38 | 10/30/2023 | Y |
| 38 | 11/6/2023 | Y |
| 38 | 11/13/2023 | Y |
| 68 | 10/2/2023 | |
| 68 | 10/9/2023 | |
| 68 | 10/16/2023 | |
| 68 | 10/23/2023 | |
| 68 | 10/30/2023 | |
| 68 | 11/6/2023 | |
| 68 | 11/13/2023 |
3 Replies
- PhilipTreacy
Super User
Hi jcastr02
I'm not following your logic.
How is Store 68 "Off of Hard to Staff for >= 4 weeks." What is Off? And it has no Y anywhere so how is it Hard to Staff?
If you are only concerned with the last 4 weeks, how can a store be "Hard to Staff >= 4 weeks"?
Are you actually counting all weeks it's hard to staff, regardless of whether it's been in the last 4 weeks?
Or is it just hard to staff in the last 4 weeks or not? No need for >= or < in your criteria?
Phil
jcastr02Apologies PhilipTreacy for not explaining throughly, for store 68 - it has not been hard to staff (thus no Y) so trying to say it has not been hard to staff for more than 4 weeks consecutively (in the last 4 weeks).
I'm trying to assess in in the relation to last 4 weeks , has a store been hard to staff:
-more than 4 weeks consecutively (in the last 4 weeks),
-less than 4 weeks consecutively (in the last 4 weeks)-been off (not been hard to staff) for more than 4 weeks consecutively.
- jcastr02
PhilipTreacy not sure if attachment helps with expected result. Thank you again
