Show previous entries within same row in a straight table

klikgevoel — Sat, 16 Nov 2024 03:48:34 GMT

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

Re: Show previous entries within same row in a straight table

Anil_Babu_Samineni — Tue, 17 Dec 2019 12:22:29 GMT

Create table as follows

Dimension : Year

Expressions :
1) Max(Year)
2) FirstSortedValue(Status, -Aggr(Max(Year), Status))
3) Concat({<Year-={"$(=Max(Year))"}>} Year & ' - '& Status, Chr(13))

topic Re: Show previous entries within same row in a straight table in App Development

Show previous entries within same row in a straight table

Re: Show previous entries within same row in a straight table