I have a date key figure (type 0DATE_KYF) in a Business Content InfoCube (0PS_C02). The query restricts the kf by a milestone characteristic value. I want to run a report that only shows dates between two entered variable values.
As it is a key figure I thought to use a condition to restrict the display of results. However the variables used in conditions do not let me define a variable of type date. I tried to enter the value in YYYYMMDD format which the 0DATE_KYF is supposed to be in, but the condition does not work. In short after troubleshooting I determined the internal value of the date key figure appears to be some serial number (31/08/2006 equates to 732555, 28/08/2006 equates to 732552 etc) which appeared to be the number of days since a particular baseline date. However further investigation shows that this values does not map to a valid date nor is the relationship a linear one.
Can anyone suggest a workable means to restrict a date key figure after entering a variable date range.
Or can anyone enlighten me as to how this serial number is translated to a date (is there a function to convert the internal number to a date)?
If you select external display characteristic - you will see the yyyymmdd format and if you select value you see the numbers....
Ideally I would recommend having the 0calday characteristic in your cube and mappping the date field to that too so that you can directly restrict on the date field using a variable...
Hope it helps...