5 Replies Latest reply: May 24, 2017 9:01 AM by Muñoz Héctor

# Year vs Previous Year in Simple Table

Hi experts,

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

SALES:

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?

Héctor

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

• ###### Re: Year vs Previous Year in Simple Table

AsofTable approach

SALES:

Year, Sales

2010, 100

2011, 200

2012, 300

2013, 400

2014, 500

2015, 600

2016, 700

2017, 800

];

AsOfTable:

Year,

'CY' as Flag

Resident SALES;

Concatenate (AsOfTable)

(Year - 1) as Year,

'PY' as Flag

Resident SALES;

• ###### Re: Year vs Previous Year in Simple Table

Like this?

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