Get the Aging Analysis report data for any Month End date of Current Year.

[jagdish_panjwani2]

Business requirement is to get the Aging Analysis report data for any Month End date of Current Year. For example, Mar 31st, Apr 30th , Oct 31st, etc;

There is standard Fiori App – Aging Analysis which have the field ‘Open on Key Date’ as shown below…

…this field has only the restricted values like First day of current year, Last day of current month, etc…month end dates are not available.

These values come from another Fiori App – Manage Date Functions

As per requirement, we need to introduce twelve new date functions in Manage Date function App which will give the Month end dates like LAST_DAY_OF_JAN_MONTH, LAST_DAY_OF_FEB_MONTH, so on.

Good thing is this App allows to create Custom date functions shown below…and maintain the month end date like 10/31/2024.

With this approach we must maintain 12 date functions referring to the month end date of current year (now it is 2024). But if we maintain like this, we must maintain for every new year 2025, 2026…so it is not a one time activity. Also, the number of date functions will keep incrementing every year…this allowed me to Re-THINK about the alternate approach/solution.

When explored I found it gives other possible values like Yesterday, First day of , Last day of,  as the Base date.

The whole objective is to make the 12 new date functions dynamic so that every year it will automatically represent the month end date…which will eliminate the need to create it every year.

-------------------------------------------

Let’s do some calculations using paper and pencil-

Last day of Dec Month = Last day of current year(Simple) [e.g; for year 2024 it is 31.12.2024 ]

The last day of the year can be used as the reference to determine the other month end dates –

Last day of Nov Month = From last day of current year minus 1 Month [ 31.12.2024 – 1 month = 30.11.2024]

…

...

Similarly, Last day of Jan Month = From last day of current year minus 11 Month [ 31.12.2024 – 11 month = 31.01.2024]

-------------------------------------------

Now, let's go to the Manage Date Function Fiori App to dynamically create date function say for month end November...it will be like –

1 month Ago From Last Day of Current year...so we are using all the dynamic variables to determine the month end dates.

When simulated, it gives 11/30/2024

Likewise, we can create other date functions to get the month end date dynamically…and it will appear in the value help of the field ‘Open on key date’ of the Aging Analysis App, which can be used to get the report data.

Thank you!

--Jagdish Panjwani