Using Conditions on Date key figures

[Former Member]

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)?

Thanks

[Former Member]

David,

I have a very similar problem and wondering if you found a workaround or solution to your problem. Please let me know.

Thanks,

Praveen

[Former Member]

The date key figure equates to a serial number where a date of 14/07/1917 equates to 700000 and each day afterwards increments the serial by one day.

I created some customer exit varialbes that converted a date from and to to the required serial numbers then used these numbers in a key figure condition.

A bit awkward but worked a treat.

[former_member184494]

David,

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...

Arun

Hope it helps...

[Former Member]

The key figure date is a restricted key figure and is used to determine a particular state of a project at a point in time. This is a business content cube. To use a characteristic instead would mean I need to completely reload the cube daily because if I created a date characteristic for that particular milestone then the other data in the report would not show through unless the date was applied to that data as well.

I have determined that the serial number is the number of days since a baseline date so will be using a customer exit variable to convert a user entered date to that internal number.