How to Calculate the Number of Days in the Selected Period for the Report (without using a customer exit!).

Table of Contents

How To Avoid Using ABAP When Calculating The Number Of Days In A Selected Time Period

 

We frequently have questions regarding calculating the number of days in a selected period.  Many customers, for a variety of reasons, take what we consider the tougher path.  Usually, this involves developing a program used in a customer exit

However, there is no need to do this most of the time and we wanted to share with you a quick and simple solution to this common SAP BW BEx Query Design problem.

Here is a six step solution that you can use today to solve this common problem:

 

1.  Create a new calculated Key Figure

2.  Create a new formula variable

New SAP BW BEx Query Variable Creation


3.  Make selections similar to what you see in this screen. We will process by replacement path and reference characteristic 0CALDAY.  0CALDAY is used in this report as the main date field and the user is prompted to enter a Date

Mandatory Interval Variable

From and Date to in the same variable (a mandatory interval variable).

 

4.  Go to the Replacement Path tab.  Here you will enter the name of the variable used on 0CALDAY and define the “replace variable with” to ‘variable’. We also change the “Use Interval” to ‘Difference’. This will give us the difference between the two selected dates.

 

Replacement Path

Replacement Path Part 2

 

5.  Finally, use this newly created formula variable in the new calculated key figure.

 

Build the formula

 

6.  Now you can insert this calculated key figure into your report. You will get a column that repeats the result for each row. This can be handy for other calculations such as calculating the number of available workdays in a period. (In our case, the user enters the number of workdays manually at the report prompt screen). Then we calculated the number of weeks times the number of workdays and used the result for our final metric.    

 

Calculated Days Result

 

If you would like to have a more in-depth tutorial on this technique you can download it here.

 

Download the Tutorial  on  How to Calculate  the Number of Days in a Period

 

Got other questions dealing with the ever present and always tricky date and time calculations required in a SAP BEx Query?  Drop your question in our comment box.  Know other ways to solve this problem, let us know in the comments below.

 

People Who This Also Read:

How 11149 Companies Fixed SAP BW DTP Errors

Guide To Maintaining SAP BW Datasources For Classification

Identifying Cause Of Data Load Error When Using A SAP BW DTP

How To Create A Complete Copy Of A SAP BW 7.3 Dataflow

 

 

Thanks

 

Download SAP BW Mindmap

Learn what SAP Business Warehouse is and what it does in under five minutes

Get this Mindmap

Victor Ayers

Victor is a SAP Certified SAP Business Warehouse and BusinessObjects Consultant with deep SAP HANA architecture expertise.

View All Articles by Victor Ayers

The SAP Blog

Subscribe to our blog and receive SAP BW Updates, demand generation, inbound marketing, sales enablement, technology and revenue generation insights and ideas delivered right to your email.