Getting MAX/MIN Date on BW Query Designer (BEx), Compatible with BusinessObjects

[mo_ezzat]

Introduction:

Hello again everyone, lately I came across a requirement where I had to find the maximum GR date on a material for a certain company code.

The maximum and minimum with regarding to date subject has been on my mind for quite a while. I searched for it a couple of years back and did not come to a significant answer but to make the date as a Key Figure then use the query conditions to retrieve the TopN based on that date Key Figure, or BottomN in case of minimum, this solution will work only on the Analyzer, or Analysis for office level, however once this query is consumed bw Web Intelligence for example, conditions will not work, consultants mostly preferred doing this on the report level.

I am more of a back end guy when it comes with altering data sets or creating calculations and there are many reasons for that, most important ones are performance and integrity, I prefer a lot doing whatever I can to avoid making calculations on the front end level, I believe that there is a way for doing everything on BW.

I had an idea of calculating the max GR date for each material on each company code, on the Query Desginer level (BEx), most importantly that is compatible with Web Intelligence.

The solution is very simple, one word, exception aggregation!

Let me show you how.

BW Version:

7.57 on HANA with BW4 Starter Package, this package restricts using any object that is not compatible during the migration to BW4, which makes it a simulation of a BW4 system.

I will be using the Inventory HANA Optimized BI Content as the base of the required calculation.

Step by Step Guide:

Creating a replacement path formula variable on the Date IOBJ.

0CALDAY is the date I will be doing this exercise on, it is mapped to the posting date in my dataflow, a GR date is the posting date restricted on movement type 101 and a not null vendor, we will come to that later.

Navigate to 0CALDAY > right click, New > Variable



You will be prompted with the screen below, give it a name and a description, make sure that the Type of variable is Formula, Processing by is Replacement Path, Ref. Char is 0CALDAY



Here is how your variable should look like:

General tab:



Replacement path tab, make sure to put the offset Length 8, and untick After Aggregation:



Currencies and Units tab, in Dimension, select Date:



Save and close.

Creating a formula on the replacement path variable with exception aggregation.

Now on the query level, create a new formula, in the Formula tab, from Groups, select Variables, select the Variable we just created ZRPF_0CALDAY



Finally, go to the General tab, Properties, Aggregation, Select Maximum, on the reference characteristic, for my scenario I needed to find the max date for each material on each company code (Max date had to reset on each company code), so I had to add both Company Code and Material, as material is not the most granular dimension in the movements ADSO.



Now in the final query, put Company and Material in rows, and the formula we just created in the columns.

Here's the output on Web Intelligence, a single line for each material and company code with the maximum GR date:



That is it, as simple as that, the following steps will be optional, if you want to make things cleaner and reusable on the Infoprovider level.

Optional Steps

A GR date is the posting date of a material movement of type 101, where the vendor is not null, hence we need to restrict that formula, this can not be done if the formula is local, on the query level, we need to create a calculated key figure then a restricted key figure.

Calculated Key Figure:

From the query designer navigate to the Infoprovider tab, expand Reusable components > right click Calculated Key Figure > New Calculated Key Figure:



Calculated Key Figure, same definition as the formula above:



Save and Close.

Restricted Key Figure:

Same place as the Calculated Key Figure, right click Restricted Key Figure node > New Restricted Key Figure:



Give it a name and a description, then navigate to the Selection tab, drag and drop the calculated Key Figure, and the dimensions you wish to restrict on, note that you must pick only the static dimensions that define your restricted Key Figure, as I mentioned above, a GR date is the Posting date for the movement type 101 where there is a vendor and coming from the movements infoprovider, the date or any possible dynamic filter should not be added here, this is from a design perspective, so that you're free to do selection periods per query for example if you restrict on 0CALDAY, however it is of course technically possible:



Now you have a new key figure, that can be restricted on whatever dimension values you need, on the ADSO level, so you can use it in any query you design.

I was able to successfully develop a stock aging report using this method, a single query returned the maximum GR date, material creation date, total stock quantity and total stock cost.

 

I hope the steps are clear enough, please feel free to post any questions you have.

Here is a link to my other blog posts:

https://blogs.sap.com/2018/12/18/monitoring-bw-loading-status-through-web-intelligence-via-email/

https://blogs.sap.com/2019/03/10/automatically-report-rejected-rows-from-error-stack/

https://blogs.sap.com/2019/05/15/plot-data-distribution-and-calculate-normal-distribution-on-webi-4....