4 Replies Latest reply on Jul 20, 2018 9:29 AM by Don Wise

    Continuous Shift Cycle/Rotation using Modulo Function

    Don Wise

      We recently implemented the attached workbook example, to support the identification, by date, of when a shift occurred.  3 different shifts with a 6-day rotation (AA,BB,CC) so A-SHIFT is on for two contiguous dates, then off for four contiguous dates, B-SHIFT is on for two, then off for four, etc.  We also used same for a 24-day rotation, 3 different shifts and got great initial results.


      We used a combination of calcs and the modulo function to achieve the correct rotation/cycle. It works very well, inclusive of date data where a date is non-contiguous.  In the Superstore Sample attached, there are some dates that are non-contiguous and the cycle/rotation disregards that issue and the rotation is good (for the 1st year).  In the production environment all dates are contiguous.


      The issue is that at the end of a complete 1st year, the cycle/rotation breaks. See below screenshot. This is regardless of whether the dates are contiguous or not. So, scaffolding by a date dimension/fact table will not resolve the issue. Any thoughts in keeping the rotation across multiple years using modulo function?


      Thanks, Don

      Screen Shot 2018-07-19 at 12.25.22 PM.png