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)

  • No labels