Question:
I have a problem that I have been struggling with for some time. It concerns writing a BEX query that computes the average for a time period when data is not available for all the days.
Ex. My time period is 8/1/2003 - 8/5/2003
Date Volume
8/1/2003 10
8/2/2003 8
8/5/2003 12
Total volume for the time period = 30
Currently I get an average of 10 (total/ #days with data). What I need is an average value of 6 (total/ #days in time period)
I have looked through the forum in the hopes of finding an answer, but so far have been unsuccessful. Does anyone have any suggestions? Any help would be greatly appreciated.
Thanks
Answer:
Use a formula variable that calculates the number of days in the month/time period and then use the formula variable to calculate the average in a formula. Sum of values /variable
Answer:
Thanks Avanie for the reply.
My problem is that I need this average on the Results line only.
Time Period = 8/1 - 8/5 which is 5 days
Date----------Volume
8/1/2003------10
8/2/2003-------8
8/5/2003------12
Result----------10 <-- this is what I get when I set Result to be an Average (total value/num days with data; ie 30/3)
Result-----------6 <-- is what I want (total value/num days in time period; ie 30/5)
I do not see any place where I can set the Result to be a formula. When I go to the Properties screen for the Volume Key Figure and then to the Calculate Result As ... area, I can just choose an option for Results from the drop down list box.
If I could get some more help in this matter, I would really appreciate it.
Answer:
The problem you have is that BW cannot calculate something on data that do not exist. in your example there is no data for 8/3/2003 and 8/4/2003.
With the formula variable solution i suggested, the average will be in a seperate column in your structure that you create as a formula.
Now if you want to have average in the same column as your data as a total line there is really only 2 options, you have to get the 8/3/2003 and 8/4/2003 to actualy be in the query.
you can do this with a structure where each row is restricted to a different date and then add a total line to the structure where you calculate the average. This is a rather inflexible sollution as your structure cannot dinamically extend itself when your date range is larger.
or you can create the other records in your Cube with zero results.
Both of these sollutions are not very nice, therefore i suggest you compromise with having your average in a seperate column as a formula.
you can always combine your data the way you want it to look in a workbook using excel functionality
Answer:
Thanks Avanie. Your answer was very helpful.