Question:
OK, I am confused about this, and I have read through posts here, and in other areas, as well as checking help and notes, etc....
I thought it was possible to take a date and bring it into BW as a Key figure, specifically as a serial number as the number of days since X date.
What I am looking for is to be able to use those date key figures to calculate the number of days between two days, etc...
However, I have followed the recomendations for putting in a date as a KF set to type "Date" as a Decimal, not as an actual date, but it does no conversion, it simply puts the date (ex: 20040112) as a number, not making any conversion.
However, online SAP Help and other places, lead me to believe that it could be converted. But in reading other areas, I got the sense that using ABAP was needed to convert the date.
I have tried using Key Figures as date (both dec and dats), and as a numeric. But no go. I have also looked into using formulas, but nothing exists.
I have tried searching for any functions (SE37) which may fit the bill, but there I have come up empty as well.
I know about creating the formula variable within BEx off the characteristic. - I have done this and it works no problem. However, I would prefer to do this on the load into BW and not on the front end.
Hopefully someone can clarify this for me, and what I have stated I am looking for isn't too confusing.
Thanks,
Stephen
_________________
As a rule I don't answer Questions in PM.
(this is not to be a d**k, but it is to allow everyone to benefit from information. Not just one person.) Ask a question in the forum and I will answer it if I can.
Answer:
Stephen,
We had a requirement to calculate the number of days between the posting date and the clearing date, and it was easier to do it in the update rules than calculate it at query time. I used the Formula Editor (even though I know that ABAP is supposed to be much more efficient) to create this small routine:
IF( IS_INITIAL( Clearing ), '', DATE_DIFF( Posting date, Clearing ) )
If the Clearing Date is not filled, then nothing is returned - if there is a value in the Clearing Date it calculates the number of days between the Posting Date and the Clearing Date.
Hope this helps - especially since you've been so generous with your advice to the rest of us.....
Answer:
SBobcat wrote "However, I have followed the recomendations for putting in a date as a KF set to type "Date" as a Decimal, not as an actual date, but it does no conversion, it simply puts the date (ex: 20040112) as a number, not making any conversion. "
If you look in the contents of the infocube (assuming you have maintained KFset to type date) for example if your date is 5/12/2004 the cube/ODS content would be something like 731733 approximately. When you select this keyfigure as is in your query and execute the query the output will be again 5/12/2004 . But when you create formulas using these dates the system will not use any conversion exits and uses equivalent number of days as stored in the cube. Actualy I found it very useful for calculating the difference between the dates and it displays the result in number of days.The most important is that you have to select the correct aggregation type in keyfigure maintenance according to your business scenario.
I never got the out put as described (20040112) just number without any date notations. Did you use this keyfigure as attribute or did you populate it in the data target?
BWquery.
Answer:
I never got the out put as described (20040112) just number without any date notations. Did you use this keyfigure as attribute or did you populate it in the data target?
BWquery.
I have the date in as a Key Figure, (and also an attribute of a characteristic - but that one is not the issue.) and in my Cube and ODS the field is always populated at 20041012 at the database level. It always has simply the date format YYYYMMDD, there is never any conversion that happens no matter what type of KF is used (date-date, date-decimal, number-dec).
I have started playing with using a Formula, but I am having problems. Though with Guest's formula it has given me an idea as to what it might be.
Though this still does not clear up my confusion as to what is supposed to be the correct method for getting the date in serially on the transfer from the source system. (In our case its coming from a 3rd party, not R/3, so another question I would have is in the case of delivered datasources which have dates as KF's does the conversion happen on the R/3 side?)
- Stephen
_________________
As a rule I don't answer Questions in PM.
(this is not to be a d**k, but it is to allow everyone to benefit from information. Not just one person.) Ask a question in the forum and I will answer it if I can.
Answer:
No you don't have to do any conversion in R/3 system.
You can directly map the fields in transferrules for R/3 data sources. But some how it does not work when you map directly one to one in update rules. Instead you have to create a routine and just say result = comm_structure-datecahracteristic IO . No more programming is required. In your case the issue is due to third party data source. Populate date as Char in transfer rules and Try the update rule method for mapping it to KF .
Bwquery
Answer:
since i enjoy kicking dead horses...
when you take a date from an attribute (or maybe even a characteristic) to a key figure via a formula variable you have 2 choices for "dimension ID". number and date.
if you choose number you get a big number which is yyyymmdd like you mentioned in your first post (your example 20040112) with which you can do mathematical manipulations; like integer division by 10000 will tell you the year or the remainder of the divisor 100 would give you the day - fun fun.
if you choose date you can perfrom date arithmetic like date1 - date2 = so many days. if you make a quick simple formula variable with customer exit to supply sy-datum you can figure out days from today. more fun.
now the bad news (except for today's date) you will need the date characteristic in the query definition along with the variable based key figure to fill the key figure value (you can hide the characteristic value if you want)
hope this helps clear up some of the confusion, or at least create some more.
_________________
when in danger or in doubt, run in circles, scream and shout.
Answer:
Hi guys,
I am very interested in this topic.
Anyone has tried for the scenario of getting the difference in number of working days for 2 dates?
Would like to know the suggestion and idea.
I have actually done it in update rules.
Best Regards,
Sean
Answer:
I never got the out put as described (20040112) just number without any date notations. Did you use this keyfigure as attribute or did you populate it in the data target?
BWquery.
I have the date in as a Key Figure, (and also an attribute of a characteristic - but that one is not the issue.) and in my Cube and ODS the field is always populated at 20041012 at the database level. It always has simply the date format YYYYMMDD, there is never any conversion that happens no matter what type of KF is used (date-date, date-decimal, number-dec).
I have started playing with using a Formula, but I am having problems. Though with Guest's formula it has given me an idea as to what it might be.
Though this still does not clear up my confusion as to what is supposed to be the correct method for getting the date in serially on the transfer from the source system. (In our case its coming from a 3rd party, not R/3, so another question I would have is in the case of delivered datasources which have dates as KF's does the conversion happen on the R/3 side?)
- Stephen
No you don't have to do any conversion in R/3 system.
You can directly map the fields in transferrules for R/3 data sources. But some how it does not work when you map directly one to one in update rules. Instead you have to create a routine and just say result = comm_structure-datecahracteristic IO . No more programming is required. In your case the issue is due to third party data source. Populate date as Char in transfer rules and Try the update rule method for mapping it to KF .
Hi Stephen
As suggested by bwquery it should work for u.
It does store date it in that number format in datatarget as suggested above and calculates perfectly in the query.
How ever it also depends on the BUSINESS MODEL u r working with say it would only be used in LOGISTICS -example for calculating delays(may be in shipment or delivery....) but if used in HR for calculating the number of days absent for an employee it may not work as it involves factory calendar and may not be so straight as seems to be.
Inshort Date as KEYFIG and only of TYPE DATS works 100%, tested and implemented.
Hope it Helps
_________________
Chetan
@ CP...
Answer:
Hi guys:
Only for future reference this might help:
/forums/viewtopic.php?t=219915
Regards
Angelo
_________________
Angelo Chatzigiannakis
SAP Consultant/BW
Athens Greece
Tel: +30 (697) 2999549
Email: hellasangel@freemail.gr