topic Re: Year vs Previous Year in Simple Table in QlikView

Year vs Previous Year in Simple Table

hector_munoz — Wed, 24 May 2017 10:56:12 GMT

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:

Year	Sales	Sales Prev Year
	3600	2800
2010	100	0
2011	200	100
2012	300	200
2013	400	300
2014	500	400
2015	600	500
2016	700	600
2017	800	700

, 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:

Year	Sales	Sales Prev Year
	1500	1200
2010	100	0
2015	600	500
2017	800	700

Any idea about how to achieve this with set analysis?

Thanks in advance!

Héctor

Re: Year vs Previous Year in Simple Table

sunny_talwar — Wed, 24 May 2017 10:57:33 GMT

Have you looked at The As-Of Table approach?

Re: Year vs Previous Year in Simple Table

sunny_talwar — Wed, 24 May 2017 11:00:24 GMT

Expression based solution can look like this

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

Re: Year vs Previous Year in Simple Table

tresB — Wed, 24 May 2017 11:04:00 GMT

Like this?

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

Re: Year vs Previous Year in Simple Table

sunny_talwar — Wed, 24 May 2017 11:07:01 GMT

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;

Re: Year vs Previous Year in Simple Table

hector_munoz — Wed, 24 May 2017 13:01:50 GMT

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