on 11-10-2009 5:54 PM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.