on 03-09-2005 11:50 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.