cancel
Showing results for 
Search instead for 
Did you mean: 

Working days in BEX between Key Figures

Former Member
0 Kudos

Hi,

I am currently looking for a way to calculate the number of working days between 2 dates in BEX.

Those dates are key figures. I do not want to use the virtual key figure since SAP does not recommend it. I also do not want to have to add an extra Key figure or caracteristic.

Can someone help ??

GCB

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Create two restricted key figures and and for that create variables using replacement path of type date. Then you can use new formula for subtracting those two rstricted key figures (date).

This will surely give you the difference between two dates.

Regds,

Uma

Former Member
0 Kudos

It's probably is too late, you may already have found a solution for this. But I had a similar issue and what I did was create a customer exit formula variable. I used the factory calendar (table TFACS) in the formula exit to calculate the number of business days in the period selected. The period selected is the one the users enter in the normal variable screen. In the customer exit I read the values for these variables and then based on the entries I determine my time range to get the business days. It works very well and it doesn't use virtual key figures where the performance impact could be terrible.

Hope this helps

PT

Former Member
0 Kudos

Hi,

how does look up your user exit, which functions did you use?

Thanks, zd.

Former Member
0 Kudos

Hi Praveen,

Could you put some more light on the same like how does the exit works....it would be of great help for all.

Thanks,

-Adhi

Former Member
0 Kudos

Hi Guylaine,

I'm not sure if this can be done in BEX, just by having a formula date1 - date2 = number of working days. Normally the number of working days between two dates depend on the calender id. May be there is a holiday in between, and what about the weekends. Weekends are not necessarily working days. If this is not important for your needs, you can go with the formula in BEX, if not, I would recommend to add a new keyfigure which will be populated in the update rules and, if it is not necessary to reload the cube, using the exit for virtual keyfigures for the historical data (just those records with no. of. w-day = 0). Hope this helps!

regards

Siggi

Former Member
0 Kudos

Hello,

We had this requirement as well and I wrote a Function Module data source to provide the working days for a period based on a specific calendar as transaction data.

We had to know the number of working days for each rep/month. so the function module provides the transaction data Period, Rep, # Days. It is a full load every day as the number of days can change and the number of days does not include the current date.

Former Member
0 Kudos

Kenneth,

The problem is that I have 'many, many' calculated or restricted key figures that need to be used in the Query Designer for the calculation of the number of working days left (here, I mean the factory calendar).

Tks

Former Member
0 Kudos

Hi,

I guess you will have to write VB script code in your query/workbook which can connect to your BW via RFC and call an FM to fetch you the workday calculation.

Since this will be not that efficient (if you call it for each row), another option would be to call a function on BW using VBScript which returns you the full calendar itself (even if you take two years, it is not more than 700 values) and refer to this array for calculation in your spreadsheet.

I have never worked with VB code on a workbook, but looking at some of the threads here, I am pretty sure this is a do-able thing.

Hope this helps,

cheers,

Ajay

Former Member
0 Kudos

I think it would be best if you could provide examples of your situation. We'll all try to help out!

Former Member
0 Kudos

Hello,

you can calculate that by using keyfigures. the keyfigure should have the datatype DATE!

We do not recommend to use the virtual keyfigures, because it could happen that the performance of report will be decrease. The reason for that is if you load additional granularity in the user exit. (means reading an additional InfoObject) This happens if you want to calculate the key figure before aggregation on a specific level. If you be aware of this and you know why the report runtime is higher, we do not have any problem by using the virtual characteristics and key figures!

hope this helps

michael

ps. please do not forget to assign points.

tom_francis2
Active Participant
0 Kudos

If you don't want to calculate it in update rules, the only option you have left is to work with excel. There is a function called networkdays. It automatically takes care of the weekends, but you can also enter extra holidays in this function.

kr,

Tom

Former Member
0 Kudos

Michael,

The key figure used are Restricted or Calculated Key Figures. Also, remember that I need the number of working days, not the number of calendar days.

This means that I need to convert those CKF or RKF in working days using the factory calendar, then subtract from the current day (converted also to factory calendar)

I tried DATE_CONVERT_TO_FACTORYDATE directly in the BW. This gives me the result I need.

My problem is : How will I have this thing to work from the Query Designer without modifying the back-end?

Guylaine

Former Member
0 Kudos

Hi,

how did you managed it in BW. I have such requirement and i need help.

Cheers

Saleh

Former Member
0 Kudos

Hi all,

I have the same task

There is a InfoObject wich shows Creation Date

In the BEX report it is needed to have Duration in work days = Current Date - Creation Date - holidays

How to du it without Virtual KF?

Thanks