Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hector_munoz_in
Valued Contributor

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

Re: Year vs Previous Year in Simple Table

Expression based solution can look like this

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

Capture.PNG

5 Replies

Re: Year vs Previous Year in Simple Table

Have you looked at The As-Of Table approach?

Re: Year vs Previous Year in Simple Table

Expression based solution can look like this

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

Capture.PNG

MVP
MVP

Re: Year vs Previous Year in Simple Table

Like this?

Capture.PNG

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

Re: Year vs Previous Year in Simple Table

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_in
Valued Contributor

Re: Year vs Previous Year in Simple Table

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

Community Browser