Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hector_munoz
Specialist
Specialist

Year vs Previous Year in Simple Table

Hi experts,


Let´s suposse we have a table in script like:

SALES:

LOAD * INLINE [

Year, Sales

2010, 100

2011, 200

2012, 300

2013, 400

2014, 500

2015, 600

2016, 700

2017, 800

];

We need to create a simple table like:

 

YearSalesSales Prev Year
36002800
20101000
2011200100
2012300200
2013400300
2014500400
2015600500
2016700600
2017800700

, but we cannot use functions like Above() as the user can select any sequence of years, e.g. 2010+2015+2017 and we woul need a table like:

 

YearSalesSales Prev Year
15001200
20101000
2015600500
2017800700

Any idea about how to achieve this with set analysis?

Thanks in advance!

Héctor

1 Solution

Accepted Solutions
sunny_talwar

Expression based solution can look like this

=Alt(Above(Sum({<Year>}Sales)) * Avg(1), 0)

Capture.PNG

View solution in original post

5 Replies
sunny_talwar

Have you looked at The As-Of Table approach?

sunny_talwar

Expression based solution can look like this

=Alt(Above(Sum({<Year>}Sales)) * Avg(1), 0)

Capture.PNG

tresesco
MVP
MVP

Like this?

Capture.PNG

Expression : Aggr(Above(Sum({<Year>}Sales)),Year)

sunny_talwar

AsofTable approach

SALES:

LOAD * INLINE [

Year, Sales

2010, 100

2011, 200

2012, 300

2013, 400

2014, 500

2015, 600

2016, 700

2017, 800

];

AsOfTable:

LOAD Year as AsOfYear,

  Year,

  'CY' as Flag

Resident SALES;

Concatenate (AsOfTable)

LOAD Year as AsOfYear,

  (Year - 1) as Year,

  'PY' as Flag

Resident SALES;

Capture.PNG

hector_munoz
Specialist
Specialist
Author

Thanks to all for your brilliant answers!!! You never stop learning!