TIMESHIFT operates within a SUM expression to make a SUM where the date dimension specified by

columnToTransform is transformed to transformationToMake and the resulting SUM made. Useful for

comparative results, e.g. TY vs LY.

Syntax:

SUM (TIMESHIFT (aggExp , columnToTransform, transformationToMake ) )

Example:

/* Produces a sum of This Year vs Last Year for 2015 on Orders */

select year( orderdate),

sum(1) as total_TY ,

sum( timeshift ( 1, orderdate, dateadd ( y , 1 , orderdate) ) ) as Total_LY

from orders

where orderdate>='2015-01-01' and orderdate<='2015-12-31'

group by year(orderdate)

## Comments

Please sign in to leave a comment.