cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation on Date fields

Former Member
0 Kudos

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.

regards,

Sunny.

Accepted Solutions (1)

Accepted Solutions (1)

dennis_scoville4
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

Hi Dennis ,

I guess with your solution it would be solved. But Can you tell me some details step to use both the dates as key figure by formulae variable. Whenever I am trying to crate formulae variable it is asking for key figure. can you guide me with steps

Regards

Sunny

dennis_scoville4
Active Contributor
0 Kudos

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

😎 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

Answers (0)