Want Rolling 12 but no 12 Months Ago time option

Tagged: Measure Window, Measures, Offset, Rolling, Time

This topic has 0 replies, 1 voice, and was last updated 8 months, 1 week ago by Frank.

Author

Posts
- February 17, 2021 at 8:09 am #622
  Frank
  Keymaster
  Topics Started - 80
  Replies Written - 53
  No problem! If you are looking to do Rolling x (commonly 12) in a column but your time selections only go back to 6 months ago you can still easily set up for Rolling x. In this example we’ll use Rolling 12 months as our target.
  
  In the example below we are comparing two columns one for this year and for last year – each represents a rolling 3 months using the “2 Months Ago” time member (highlighted in blue) and we want to change to Rolling 12. Ok – I know – we want rolling 3 but we’re using rolling 2?? It’s because we are using Current Month in the To column, so we only need to back 2 months from there in the From to get a total of 3 months. If you wanted to get the last complete 3 months you would use 3 Months Ago in the From and Previous Month in the To. As you can see in the example – we only have time members back to 6 Months Ago (in red) so we can’t get to Rolling 12….
  
  So – how to get to Rolling 12? We’re going to use the Offset option below the Period in the From. To do this we are going to do a couple of things:
  1. Set the From Period to Current Month – not required but I find in simpler when using offsets to the have the From and To periods set to the same value – with the To set to the Month (or week, or day) you want to end on.
  2. We’re going to enter negative 11 (-11) in the Offset for the From. This will cause Stratum to back up 11 months from the Current period giving us a total of 12 months. Since it’s -11 from the Current month, as the Current month changes the months will roll with the changes (REO reference….if you don’t get it, you’re younger than me….)
  
  You can see numbers 1 and 2 in red in the example below. Simple to do and you can easily set up rolling quarters, months, weeks or days for any number back in Stratum. Also remember you can use CTRL click to highlight multiple selections in the Edit Measure Window and change the any of the common values on measures simultaneously.
  - This topic was modified 8 months, 1 week ago by Frank.
Author

Posts