Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
interesting pivot table questing
let us consider the following access table
mod Region rev qty 020 china 20 10 023 india 30 10 020 japan 40 8 020 china 10 2 (database table) , and we define the query: SELECT [mod], Region, Sum (rev) AS srev, Sum(qty) AS sqty, srev/sqty AS cru FROM Table1 GROUP BY [mod], Region; we then use the pivot table to get CRU , we get the following pivot table result: region:(All) mod 020 Data sum of srev sum of sqty sum of CRU total 70 20 7.5 the cru is 7.5, which is not correct, if we defined a calculated item called CRU(srev/sqty), then the cru is 3.5, which is correct. However, if we define the region to be china, the result returned by pivot table using database query is correct. and can be seen as follows in pivot table : region:(All) mod 020 Data sum of srev sum of sqty sum of CRU total 30 12 2.5 The reason is that CRU can't be aggregated. so how to design the datbase query in the pivot table, when user aggragates region, mod or whatever, we can still return the cru correctly. .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
interesting pivot table questing
Why not point your pivot table straight at the table and
use a calculated field in the pivot? cheers Simon -----Original Message----- let us consider the following access table mod Region rev qty 020 china 20 10 023 india 30 10 020 japan 40 8 020 china 10 2 (database table) , and we define the query: SELECT [mod], Region, Sum (rev) AS srev, Sum(qty) AS sqty, srev/sqty AS cru FROM Table1 GROUP BY [mod], Region; we then use the pivot table to get CRU , we get the following pivot table result: region:(All) mod 020 Data sum of srev sum of sqty sum of CRU total 70 20 7.5 the cru is 7.5, which is not correct, if we defined a calculated item called CRU(srev/sqty), then the cru is 3.5, which is correct. However, if we define the region to be china, the result returned by pivot table using database query is correct. and can be seen as follows in pivot table : region:(All) mod 020 Data sum of srev sum of sqty sum of CRU total 30 12 2.5 The reason is that CRU can't be aggregated. so how to design the datbase query in the pivot table, when user aggragates region, mod or whatever, we can still return the cru correctly. .. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Questing regarding Date Stamp | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |