First of all, thank you! This was helpful. I have a couple of questions going forward:
1. The change/no change calculation will remain the same with more snapshots. That is, for equipment A1 the outage date has changed from 1st to 2nd snapshot. But now when I take a 3rd snapshot the outage date is same as the one in 2nd snapshot (5 march, 2019). In this case the third snapshot in the bar will also be showing blue (and as changed) which is not required as the outage date has not changed from the last snapshot. And all subsequent snapshots will also be having blue which might be misleading.
Is there a way to tag the changes that happen from the last snapshot to latest snapshot only while keeping the tags for the previous changes as well? Like below snapshot - (i have added more snapshots to the data for testing in attached workbook) -
Have added a twbx for reference.
Again, thank you so much for looking into this! I have spent quite a bit of time on this and have tried using lookups but havent been able to get it right.
Workbook for community.twbx 211.5 KB
Hope you are doing well.
I have been looking at rolling windows for comparison of only two snapshot dates at a time but haven't found a solution yet. Would you know if this kind of comparison (with only 2 snapshots at a time) be possible?
Many thanks to let me know!
Sorry about the late response. I was struggling over the last couple of dates with the solution.
Please see my updated solution attached as well as the screenshot of steps below
Hopefully this could help you out.
Workbook for community_v10.2.twbx 230.4 KB
Apologies for the late reply. I was trying your solution with different changes in data.
I believe the calculations inherently assume that an outage date will always get moved to a future date? - For ex. If I move an outage date from the year 2022 to the year 2019 the change does not get tagged. In fact the old outage date in 2022 gets tagged as changed (where ever the outage date is max that particular line item gets tagged as a change). Would it be possible to tag a change irrespective of what the change is (forward or backward moving of a date)?
Again, I apologize for the back and forth. This is my first post on the community. I should have clarified all my doubts in the original post.
Thank you Zhang!
Apparently outage dates can only be moved in to the future and not backwards, so this works perfectly for me!