Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Expression based solution can look like this
=Alt(Above(Sum({<Year>}Sales)) * Avg(1), 0)
Have you looked at The As-Of Table approach?
Expression based solution can look like this
=Alt(Above(Sum({<Year>}Sales)) * Avg(1), 0)
Like this?
Expression : Aggr(Above(Sum({<Year>}Sales)),Year)
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;
Thanks to all for your brilliant answers!!! You never stop learning!