Search code examples
filterreportwebi

Show only last occurence of the field

I have an issue with a report I am trying to make in SAP. The problem is that I want to only show each SR NUM only once. But there are many appearances in my report. I saw that each number has multiple activities and comments and that is why there are appearing more than once. The thing is that I only need the last appearance based on date for each SR Num and there is no filter that can help me with this. I also tried Ranking but I do not have a metric and created a new variable finding max date for each sr num. That also did not work as there are multi values.

Please help!

For example i have 3 columns sr num, date and comments. The first has 3 different nums but multiple times and the dates are all different as the comments.I need to only keep each sr num once with the most recent date and comment

Solution

  • I created some sample data in a free-hand SQL query which yields this...

    enter image description here

    You will need to find the maximum date for each SR Num and then only show that row for each SR Num. I used two variables to achieve this.

    Var Max Activity Date...

    =Max([Activity Date]) In ([SR Num])

    Var Is Max Activity Date...

    =If([Activity Date] = [Var Max Activity Date]; 1; 0)

    enter image description here

    Finally add a table filter to only show the rows where the Activity Date is the Max Activity Date for each SR Num.

    Var Is Max Activity Date Eq

    You do not need the variables in your table in the end. I just put them there in order to visualize what is going on. That's it.

    enter image description here

    Noel