Hi all,
I've got a question for which I can't seem to find the right solution. In a straight table I want to show in a specific cell the previous entries for each record. For example, Company A is in 2019 a Customer, while in 2018 the company was still a Prospect, and in 2017 they were Unknown. As such, the table should look something like follows:
| Name | Year | Status | Previous years |
| Company A | 2019 | Customer | 2017 Unknown 2018 Prospect |
| Company B | 2019 | Removed | 2015 Prospect 2016 Customer 2017 Customer 2018 Customer |
Note that the current year (2019) is selected as a filter.
Some sample data
LOAD * INLINE [
Name, Year, Status
Company A, 2017, Unknown,
Company A, 2018, Prospect,
Company A, 2019, Customer,
Company B, 2015, Prospect,
Company B, 2016, Customer,
Company B, 2017, Customer,
Company B, 2018, Customer,
Company B, 2019, Removed,
];
What I tried so far is creating another dimension with data previous to 2019 and using a custom variable called Mapper which was set to 2019. Second, in the Previous years dimension of the straight table using an IF-statement: if(Mapper = Year, Status). However, this doesn't work.
Another method I tried was setting the table to a static state. Then using FirstSortedValue(Status, -Year) in one measure of the table and FirstSortedValue(Status, -Mapper) as a second measure. This however returns a null is more than one previous value is found.
Much appreciated for the help
