KB5603: How to create and use transformation metrics in MicroStrategy Developer 9.4.x-10.x?

The following is an example of a transformation metric using the Vmall Project. Perform the following steps.

• Follow the steps in KB4374 to create a transformation on the attribute year.
• Create a copy of the metric 'Dollar Sales' and name it 'Last Years Dollar Sales'.
• Open 'Last Years Dollar Sales' in the metric editor and highlight the 'Transformation' definition as shown below:
  

• Drag and drop the transformation 'Last Year' into the 'Transformations' window as shown below:
  

• Save and Close the metric.
• Create a report with the attribute 'Category', the metrics 'Dollar Sales' and 'Last Years Dollar Sales' and the filter 'Year=1998' as shown below.
  

• Run the report. The report is displayed as shown below.
  

• The report has the dollar sales for 1998 in the column under 'Dollar Sales'. This is created with the following Structured Query Language (SQL) code:
  create table ZZTEE006V77MD000 (
    Category_Id LONG,
    DOLLARSALES DOUBLE)
  insert into ZZTEE006V77MD000
  select a11. AS Category_Id,
    sum(a11.) as DOLLARSALES
  from a11
  where  a11. in (1998)
  group by   a11.
   
  
• The report also shows the dollar sales for the same time last year in the column under 'Last Years Dollar Sales'. This is created with the following SQL (note the change in the where clause):
  create table ZZTEE006V77MD001 (
    Category_Id LONG,
    LASTYEARSDOL DOUBLE)
  insert into ZZTEE006V77MD001
  select a11. AS Category_Id,
    sum(a11.) as LASTYEARSDOL
  from a11,
    a12
  where a11. = (a12. - 1)
  and a12. in (1998)
  group by   a11.
  

 
A transformation metric is useful in showing change over the course of time. For example, it can be used to display change in sales from year-to-year or change in sales from the current day to the same day last year.