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:

Data Loaded in DSO:

Query Format :

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.

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

Step 4–Save the condition and Query.

Query Output: To show first Record

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.

Query Output:To show Latest Record

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.