| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OLAP Data Cube Question
Is there a way to have a data cube create an "average" in Excel? Looking at
the options, it only lists sum, min, max, & count. I really need an average function. Is it possible to write a macro to do it w/ an average? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OLAP Data Cube Question
Nope... To the best of my knowledge if you create a cube those are your only
options. I assume that creating the pivot table directly in Excel (foregoing the Cube) is not an option? -- HTH... Jim Thomlinson "Thomas L" wrote: Is there a way to have a data cube create an "average" in Excel? Looking at the options, it only lists sum, min, max, & count. I really need an average function. Is it possible to write a macro to do it w/ an average? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OLAP Data Cube Question
Well, I built a pivot table and it looked fine, but then my director saw it,
and she wanted an easier way to pull reports. She wants to be able to click the date option, and have to only click on the quarter or month if she wants to pull that report, instead of having to click each individual date for that time period. If possible to do that with the pivot table, that will be even better. "Jim Thomlinson" wrote: Nope... To the best of my knowledge if you create a cube those are your only options. I assume that creating the pivot table directly in Excel (foregoing the Cube) is not an option? -- HTH... Jim Thomlinson "Thomas L" wrote: Is there a way to have a data cube create an "average" in Excel? Looking at the options, it only lists sum, min, max, & count. I really need an average function. Is it possible to write a macro to do it w/ an average? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OLAP Data Cube Question
So you have a date field which is actual dates. Right click on that field and
select Group. You can group by Day, Month, Quarter and/or Year. It is a great way of aggregating your data. -- HTH... Jim Thomlinson "Thomas L" wrote: Well, I built a pivot table and it looked fine, but then my director saw it, and she wanted an easier way to pull reports. She wants to be able to click the date option, and have to only click on the quarter or month if she wants to pull that report, instead of having to click each individual date for that time period. If possible to do that with the pivot table, that will be even better. "Jim Thomlinson" wrote: Nope... To the best of my knowledge if you create a cube those are your only options. I assume that creating the pivot table directly in Excel (foregoing the Cube) is not an option? -- HTH... Jim Thomlinson "Thomas L" wrote: Is there a way to have a data cube create an "average" in Excel? Looking at the options, it only lists sum, min, max, & count. I really need an average function. Is it possible to write a macro to do it w/ an average? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OLAP Data Cube Question
When I grouped it, it created 2 new colomns, is there a way to group it where
the drop down for Date has the breakdown....my only worry is the person that is going to be using this report, she isn't Excel savvy, so I don't want to have something that is going to take her a while to learn how to use... ex. 2005 Qtr 1 Jan 1 2 Feb Mar etc... "Jim Thomlinson" wrote: So you have a date field which is actual dates. Right click on that field and select Group. You can group by Day, Month, Quarter and/or Year. It is a great way of aggregating your data. -- HTH... Jim Thomlinson "Thomas L" wrote: Well, I built a pivot table and it looked fine, but then my director saw it, and she wanted an easier way to pull reports. She wants to be able to click the date option, and have to only click on the quarter or month if she wants to pull that report, instead of having to click each individual date for that time period. If possible to do that with the pivot table, that will be even better. "Jim Thomlinson" wrote: Nope... To the best of my knowledge if you create a cube those are your only options. I assume that creating the pivot table directly in Excel (foregoing the Cube) is not an option? -- HTH... Jim Thomlinson "Thomas L" wrote: Is there a way to have a data cube create an "average" in Excel? Looking at the options, it only lists sum, min, max, & count. I really need an average function. Is it possible to write a macro to do it w/ an average? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
OLAP Data Cube Question
Try setting your grouping to Year Quarter and Month. Drag the year and
quarter to the top just above the table. This is where she can select the appropriate period. Your dispayed data should now be just the months. If she double clicks on any number in the table a new sheet will be spawned showing her all of the info for that month. Try dragging the memebers of the time dimension around until you get something you like. It takes a little practice but you will get the hang of it. I also recommend adding a format to the pivot table to make it easier for the user to understand what they are seeing. Too many numbers are just data. Well organized reports make the numbers into information. That takes practice. One day I am hoping to be good at it myself. -- HTH... Jim Thomlinson "Thomas L" wrote: When I grouped it, it created 2 new colomns, is there a way to group it where the drop down for Date has the breakdown....my only worry is the person that is going to be using this report, she isn't Excel savvy, so I don't want to have something that is going to take her a while to learn how to use... ex. 2005 Qtr 1 Jan 1 2 Feb Mar etc... "Jim Thomlinson" wrote: So you have a date field which is actual dates. Right click on that field and select Group. You can group by Day, Month, Quarter and/or Year. It is a great way of aggregating your data. -- HTH... Jim Thomlinson "Thomas L" wrote: Well, I built a pivot table and it looked fine, but then my director saw it, and she wanted an easier way to pull reports. She wants to be able to click the date option, and have to only click on the quarter or month if she wants to pull that report, instead of having to click each individual date for that time period. If possible to do that with the pivot table, that will be even better. "Jim Thomlinson" wrote: Nope... To the best of my knowledge if you create a cube those are your only options. I assume that creating the pivot table directly in Excel (foregoing the Cube) is not an option? -- HTH... Jim Thomlinson "Thomas L" wrote: Is there a way to have a data cube create an "average" in Excel? Looking at the options, it only lists sum, min, max, & count. I really need an average function. Is it possible to write a macro to do it w/ an average? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving OLAP Data Cube Into and Out Of Excel | Excel Discussion (Misc queries) | |||
| olap data cube | Excel Discussion (Misc queries) | |||
| OLAP Data Cube | Excel Worksheet Functions | |||
| suppress warning and locate cube in a pivot table from olap cube | Excel Discussion (Misc queries) | |||
| Change OLAP Cube Data Source | Excel Programming | |||
![Thomas L[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
