Skip to Content

How to show First and Latest Record at BEx Level

Introduction:

Lately i came across so many threads where the requirement was to show the first or latest record at bex level.I tried to replicate the same scenario in my system.Here i will be showing the sales of a particular plant on first and last day of particular month.


Data Sheet:

2.JPG

Data Loaded in DSO:

3.JPG

Query Format :

8.JPG

Here we have Converted the Characteristic 0calday into Keyfigure (Date KF) with the help of formula variable with replacement path.

Make sure to choose Unit/Dimension as Number.I am not giving a walkthrough on the steps of the same as it has been discussed so many times.

Refer the below document for step by step navigation:

Convert a Characteristic into a Key Figure (BEx)



Approach to show the First Record:

Step 1–Hit the condition tab at BEx query designer–Right click on empty space–New condition–Edit


Step 2–Hit the New tab—Keyfigures–Date KF–Operator–Bottom N–Values = 1.Then Hit the Transfer tab.Refer the below screenshot.

9.JPG

Step 3–Hit the Characteristic Assignment Tab..Choose the options as per the screenshot below.

7.JPG

Step 4–Save the condition and Query.

Query Output: To show first Record

10.JPG

Approach to show the Latest Record:


All the steps to get the latest record will be same except with the slight change in conditions tab.

Instead of Operator Bottom N we will choose Top N.Refer the below screenshot.

12.JPG

Query Output:To show Latest Record

11.JPG

Conclusion:It’s apparent that after following the above approach we got the desired result as per our data stored in DSO.

Hope this helps.All suggestions and comments are welcome.

87 Comments
You must be Logged on to comment or reply to a post.