Rolling window - Difference in sales of current with previous month

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Rolling window - Difference in sales of current with previous month

I want to calculate the difference in sales between currrent and preivous month.

I am trying it with OLAP functions, but couldn't get it working.

Below is the dataset

Product | Year | Qtr | Month | Sales
P1
2013 1 JAN 2000
P2
2013 2 APR 3000
P3
2013 3 JUL 4000
P4
2013 4 OCT 5000
P2
2014 1 FEB 6000
P3
2014 2 MAY 8000
P1
2014 3 AUG 5000
P4
2014 4 NOV 6000
P4
2015 1 MAR 7000
P1
2015 2 JUN 1000
P1
2015 3 SEP 2000
P3
2015 4 DEC 3000
P1
2013 1 JAN 5000
P2
2013 2 APR 2000
P3
2013 3 JUL 5000
P4
2013 4 OCT 6000
P1
2014 1 FEB 7000
P2
2014 2 MAY 1000
P3
2014 3 AUG 2000
P4
2014 4 NOV 3000
P1
2015 1 MAR 5000
P2
2015 2 JUN 2000
P3
2015 3 SEP 3000
P4
2015 4 DEC 5000
P2
2013 1 JAN 2000
P1
2013 2 APR 3000
P4
2013 3 JUL 4000
P3
2013 4 OCT 5000

 

Where I want something like

 

Product Year Qtr Month Sales Diff

P1 2013 1 JAN 2000 2000 <-- 2000-0
P2
2013 2 APR 3000 -1000 <-- 1000-2000 
P3
2013 3 JUL 4000  1000 <-- 4000-3000
...

 

How can I do it?

 

Thanks,

Ali

1 REPLY
Enthusiast

Re: Rolling window - Difference in sales of current with previous month

SyntaxEditor Code Snippet

select
pkey,
(Sales - PreviousValue) DAY(4) TO MINUTE
from(select
pkey,
   Sales,
    min(Sales) over (partition by pkey,year ,qtr  /*ORDER BY RULE_TYPE_NUMBER */ROWS BETWEEN 1 PRECEDING and 1 PRECEDING) as PreviousValue
    from  table    )
it looks like your table doesnot have good primary key

 

 

 

http://community.teradata.com/t5/Database/How-to-get-previous-row-values/td-p/40057