Skip to Content
author's profile photo Former Member
Former Member

to retrieve the oldest date in the report

I have a requirement to retrive the oldest date in the report, I am currently working in BW 3.5

The scenario is as below

I have a char infoObject creation date which retrieves the date

I have a char infoobject status which retrieves open or close

For eg in my cube I have 5 records

3 records has the status open and 2 records has the status closed

When I adding the InfoObject "creation date" which has filtered for the open status , I am getting the result correctly with 3 records

Now my requirement is I need to have only one row which should get oldest "creation date" out of the 3 records as below

Rec1 10.10.2006

Rec2 25.11.2007

Rec3 20.08.2009

I need only one count in the creation date which should show me 10.10.2006

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 27, 2010 at 04:56 PM

    Try creating a formula, a replacement path variable with 'creation_date', Currency/Unit as Date,exception aggregation First value based on a suitable char. Else, need to write a routine at provider level to fetch only the oldest record.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 27, 2010 at 06:26 PM

    Hi Pavan.

    Can you model the creation date as a key figure as well as having it as a char? If so, you can create a condition on the key figure, to only give you the "Bottom 1" record.

    I think in 3.5 you cannot use a formula variable to read the value of the char in the row, but if you can, you can do it like that with a calculated key figure where you use the formula var and then put a condition on this ckf.

    regards

    Jacob

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.