Creating a difference measure on a time series

[jleefjcapital]

Hello,

I'm trying to create a difference measure on a time series where I would like to most current period to represent the change over the previous period.   For instance, if the original time series is as follows:

2015    10

2014      8

2013     5

2012    4

I would like to create a new series:

2015    2

2014    3

2013    1

How would I go about doing this? 

Thank you,

[sunny_talwar]

Try this script:

Table:

LOAD * Inline [

Year, Value

2015, 10

2014, 8

2013, 5

2012, 4

];

FinalTable:

LOAD Year,

  Value,

  Value - Alt(Peek('Value'), Value) as Delta

Resident Table

Order By Year;

DROP Table Table;

Output:

HTH

Best,

Sunny

[jleefjcapital]

Thank you, Sunny.  I will try this.  

I'm also trying to compute the % change over time, but the statement I'm using is going backwards.  

Rather than taking the % change from 2014 to 2015, it's taking the % change from 2015 to 2014.   My statement is as follows-

Num(Sum(RETAILTRADE_MSA)/If(Sum(RETAILTRADE_MSA) > 0, Above(Sum({<DATE=>} RETAILTRADE_MSA)))-1, '#,##0.%')

Not sure what I'm doing wrong. 

Thanks, again!

[sunny_talwar]

Either change the sort order from descending to ascending or if you want to keep the order descending change your expression to this:

Num(Sum(RETAILTRADE_MSA)/If(Sum(RETAILTRADE_MSA) > 0, Below(Sum({<DATE=>} RETAILTRADE_MSA)))-1, '#,##0.%')

HTH

Best,

Sunny

[jleefjcapital]

I just realized that too.   Thank you!  It worked!

[sunny_talwar]

Awesome, I am glad you got it to work. I suggest closing this thread by marking the correct answer please.

Thanks,

Sunny

[jleefjcapital]

How do I mark it correct? 

[sunny_talwar]

See the screenshot:

[jleefjcapital]

I was looking for that, but I don't see it.

[jleefjcapital]

Sunny,

Just one small follow up question.   If I wanted to use the application (front end) to create the difference or delta variable, would this be the correct script to use? 

Num(sum(RETAILTRADE_MSA) - below(sum(RETAILTRADE_MSA))