Microstrategy Blog

Last Year Transformation:

SELECT DISTINCT
    y1.YEAR_ID          YEAR_ID
   ,(y1.YEAR_ID-1)   LAST_YEAR_ID
FROM LU_YEAR y1

The assumption is you would create this as a VIEW. Another option is, of course, to just add a column to your LU_YEAR table that’s the last (prior) year.  Something also to keep in mind…this Transformation only works if you’re dealing with data that is at the YEAR LEVEL already.  If you’re dealing with a Warehouse that compiles data at the monthly level, and you want the ability to actually compare month to month for prior year, it’s best to develop this at the bottom of the heirarchy, in the below case, it’s month.

SELECT DISTINCT
    m1.MONTH_ID              MONTH_ID
    ,(m1.MONTH_ID-100)   MONTH_PERIOD_ID
FROM LU_MONTH m1