Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jleefjcapital
Contributor

Creating a difference measure on a time series

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,

10 Replies
MVP
MVP

Re: Creating a difference measure on a time series

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:


Capture.PNG

HTH

Best,

Sunny

jleefjcapital
Contributor

Re: Creating a difference measure on a time series

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!

MVP
MVP

Re: Creating a difference measure on a time series

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
Contributor

Re: Creating a difference measure on a time series

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

MVP
MVP

Re: Creating a difference measure on a time series

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

Thanks,

Sunny

jleefjcapital
Contributor

Re: Creating a difference measure on a time series

How do I mark it correct? 

MVP
MVP

Re: Creating a difference measure on a time series

See the screenshot:

Correct.PNG

jleefjcapital
Contributor

Re: Creating a difference measure on a time series

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

jleefjcapital
Contributor

Re: Creating a difference measure on a time series

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))