Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
OLAP Pivot Table Calculations
How can you add a column to an OLAP pivot table to show the difference
between values in two columns. We need to show data for two different dates and then show the change (financial reporting). Unfortunately, it's not a year over year or quarter over quarter, or any other standard timeframe, otherwise we could calculate it in the SSAS cube. Thanks in advance for any insight. Malinda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
OLAP Pivot Table Calculations
Hi,
The OLAP fact has no impact on this. Suppose you have pivot table with a column field called year with two years, and a row field called department and a data field called Amount. Double-click the Sum of Amount field button, and in the Pivot Table Field box click Options. Open the Show Data as drop down and choose Difference from. Then pick Year field under Base field and pick either year in the Base item area. Remember to click Yes if this information helps. -- Thanks, Shane Devenshire "Malinda" wrote: How can you add a column to an OLAP pivot table to show the difference between values in two columns. We need to show data for two different dates and then show the change (financial reporting). Unfortunately, it's not a year over year or quarter over quarter, or any other standard timeframe, otherwise we could calculate it in the SSAS cube. Thanks in advance for any insight. Malinda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
OLAP Pivot Table Calculations
Thanks for the response. I am using Excel 2007 with an OLAP pivot table and
I do think there is a difference with the OLAP pivot table (for one thing it is a "Value Field Setting" not data field). When I follow these steps, I get #N/A as all of my results. I did this with the Adventure Works cube by putting Product Categories on Rows, Calendar on Columns and Order Quantity on values. Malinda "ShaneDevenshire" wrote: Hi, The OLAP fact has no impact on this. Suppose you have pivot table with a column field called year with two years, and a row field called department and a data field called Amount. Double-click the Sum of Amount field button, and in the Pivot Table Field box click Options. Open the Show Data as drop down and choose Difference from. Then pick Year field under Base field and pick either year in the Base item area. Remember to click Yes if this information helps. -- Thanks, Shane Devenshire "Malinda" wrote: How can you add a column to an OLAP pivot table to show the difference between values in two columns. We need to show data for two different dates and then show the change (financial reporting). Unfortunately, it's not a year over year or quarter over quarter, or any other standard timeframe, otherwise we could calculate it in the SSAS cube. Thanks in advance for any insight. Malinda |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
OLAP Pivot Table Calculations
malinda,
did you ever figure this out? i'm dealing with the same issue. thanks, arthur "Malinda" wrote: Thanks for the response. I am using Excel 2007 with an OLAP pivot table and I do think there is a difference with the OLAP pivot table (for one thing it is a "Value Field Setting" not data field). When I follow these steps, I get #N/A as all of my results. I did this with the Adventure Works cube by putting Product Categories on Rows, Calendar on Columns and Order Quantity on values. Malinda "ShaneDevenshire" wrote: Hi, The OLAP fact has no impact on this. Suppose you have pivot table with a column field called year with two years, and a row field called department and a data field called Amount. Double-click the Sum of Amount field button, and in the Pivot Table Field box click Options. Open the Show Data as drop down and choose Difference from. Then pick Year field under Base field and pick either year in the Base item area. Remember to click Yes if this information helps. -- Thanks, Shane Devenshire "Malinda" wrote: How can you add a column to an OLAP pivot table to show the difference between values in two columns. We need to show data for two different dates and then show the change (financial reporting). Unfortunately, it's not a year over year or quarter over quarter, or any other standard timeframe, otherwise we could calculate it in the SSAS cube. Thanks in advance for any insight. Malinda |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
OLAP Pivot Table Calculations
Unfortunately, I have not found a solution to this except to save the pivot
table as formulas (OLAP tools) and then add the calculation. The only problem with this approach is that if any members are added to the dimensions you have included, you won't get those in future refreshes of the data. Malinda "arthur" wrote: malinda, did you ever figure this out? i'm dealing with the same issue. thanks, arthur "Malinda" wrote: Thanks for the response. I am using Excel 2007 with an OLAP pivot table and I do think there is a difference with the OLAP pivot table (for one thing it is a "Value Field Setting" not data field). When I follow these steps, I get #N/A as all of my results. I did this with the Adventure Works cube by putting Product Categories on Rows, Calendar on Columns and Order Quantity on values. Malinda "ShaneDevenshire" wrote: Hi, The OLAP fact has no impact on this. Suppose you have pivot table with a column field called year with two years, and a row field called department and a data field called Amount. Double-click the Sum of Amount field button, and in the Pivot Table Field box click Options. Open the Show Data as drop down and choose Difference from. Then pick Year field under Base field and pick either year in the Base item area. Remember to click Yes if this information helps. -- Thanks, Shane Devenshire "Malinda" wrote: How can you add a column to an OLAP pivot table to show the difference between values in two columns. We need to show data for two different dates and then show the change (financial reporting). Unfortunately, it's not a year over year or quarter over quarter, or any other standard timeframe, otherwise we could calculate it in the SSAS cube. Thanks in advance for any insight. Malinda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OLAP Pivot Table problem | Excel Discussion (Misc queries) | |||
2007 Pivot Table or OLAP Add-in | Excel Discussion (Misc queries) | |||
how do I identify the OLAP datasource of a pivot table | Excel Discussion (Misc queries) | |||
How to make my own formula for OLAP Pivot table? | Excel Discussion (Misc queries) | |||
Pivot Table - OLAP Cube | Excel Discussion (Misc queries) |