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

Calculation on Date fields

Hi All,

I have WBS_basic start and wbs_basic finish as Date fields. These are display attribute of Wbs_elemt. Now I need a key figure called Average age which would be the average of wbs_basic start and wbs_basic finish. Can it be done at the query level.

Please suggest what should I do.



Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Nov 10, 2009 at 06:02 PM

    Create both dates as Formula Variables, so that they are treated as Key Figures in the query. Then create a Calculated Key Figure, based on the Formula Variables for these two dates, subtracting the basic start date from the basic finish date to get a difference in days.

    Add a comment
    10|10000 characters needed characters exceeded

    • Here are the steps:

      1) Go into your query in BEx Query Designer

      2) Make sure that the date fields you want to use are in the Rows quadrant

      3) In the Columns quadrant, right-click on Key Figures and click on New Formula

      4) Right-click on Formula Variable in the lower left quadrant of the Formula Editor and click on New Variable

      5) In the General tab, enter a Description, Technical Name, change the Processing By to Replacement Path and use the find the same date field in the dropdown and select it for Reference Characteristic

      6) In the Replacement Path tab, make sure the Replace With is set to Key

      7) In the Currency/Unit tab, change the Dimension Settings to Date

      8) Save the Variable and click OK

      9) Right-click on your new Formula Variable and click on Insert Into Formula

      10) In the General tab of the Formula, give a meaningful Description

      11) Go to the Display tab and make sure the Always Hide radio button is selected

      12) Save the Formula

      13) Repeat Steps 3-12 to create a Formula Variable for your second date

      14) Right-click on Calclated Key Figure, in the left pane, and then click on New Calculated Key Figure

      15) Give your Calculated Key Figure a meaningful Description and Technical Name

      16) In the lower left quadrant of the Calculated Key Figure screen, find your first Formula Variable and drag-and-drop it into the Detail View

      17) Add a minus sign after the first Formula Variable in the Detail View

      18) Repeat Step 16 for the second Formula Variable

      19) Go to the Display tab and set the Number of Decimal Places to 0

      20) Save your new Calculated Key Figure

      21) Drag-and-drop your new Calculated Key Figure to the Key Figures structure in the Rows pane

      22) Save you query and execute it to test this new functionality

      BTW...Anywhere where you create a new object (Formula, Formula Variable or Calculated Key Figure), it may not go directly into the edit screen. If this happens, just right-click and select edit for the object and then you'll be in the edit screen.

      Edited by: Dennis Scoville on Nov 10, 2009 1:18 PM

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.