Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Week Over Week Change in Date

 

Hello.

I have a data set that is primarily activities and dates. Each week we update the data, but I do not replace the old data. Instead, I use the imported file name to generate a work week number. This allows me to see exactly what date each activity fell on each week.
Vastly simplified, the data looks a little like this.

Unique IdentifierWork Week ActivityStart Finish
1WW01 Design12/15/20211/31/2022
2WW01 Money2/1/20222/18/2022
3WW01 Construction2/19/202210/5/2022
4WW01 Complete10/6/202211/15/2022
1WW02Design12/15/20212/4/2022
2WW02Money2/7/20222/22/2022
3WW02Construction2/25/202210/5/2022
4WW02Complete10/6/20221/15/2023
1WW03Design12/15/20212/4/2022
2WW03Money2/7/20226/22/2022
3WW03Construction2/19/20226/15/2022
4WW03Complete12/26/20221/30/2022

I am trying to create a performance against schedule week over week. I want an indicator that displays every instance of a date change, both pull in or push out. I'm currently using a column for 'push' that indicates a '1' every time a date slips and a 'pull' column that indicates a '1' each time a date pulls in, week over week-- this both provides true/false logic as well as lets me count the number of pulls or pushes.

Unique IdentifierWork Week ActivityStart FinishStart PushStart PullFinish PushFinish Pull
1WW01 Design12/15/20211/31/2022    
2WW01 Money2/1/20222/18/2022    
3WW01 Construction2/19/202210/5/2022    
4WW01 Complete10/6/202211/15/2022    
1WW02Design12/15/20212/4/2022  1 
2WW02Money2/7/20222/22/20221 1 
3WW02Construction2/25/202210/5/20221   
4WW02Complete10/6/20221/15/2023   1
1WW03Design12/15/20212/4/2022    
2WW03Money2/7/20226/22/2022  1 
3WW03Construction2/19/20226/15/2022 1 1
4WW03Complete12/26/20221/30/20221  1

I would also like to indicate change consistency, meaning, I would like to identify consistent pushes 4 weeks in a row, 3 weeks in a row, and 2 weeks in a row.


I have this all built as a work around, but it isn't an ideal solution. My current solution identifies the max work week as the current work week and references the current work week number. This isn't ideal for my situation, because I can't show where we were in past work weeks. I need to be able to produce the visual below.

What I have:

What I want:


How do I best show changes week over week without the current week being fixed? I need to show historical trends, ie an ability to look back at a prior work week's statistics from a later work week. Currently I can't do this because by establishing the current week as the max, all calculations are relative.

2 Replies