I am trying to create a simple matrix table, with periods (i.e. Feb-2011) as the column labels and Customers as the row labels. The cells should contain the sum of sales amounts (by customer and period). This sounds simple I think, but I haven't found the best way to go about it. Any help is appreciated...
If I follow what you're after try to following basic example:
Create a new Pivot Table, select your Customers as the first dimension and Period as the second (you can use the 'Add calculated dimension' option if you need to format a date into a particular style eg monthname(DateField) gives Feb-2011 from 01/02/2011) Then as your expression enter something along the lines of sum(Sales) and click OK. This will create a Pivot with 2 dimensions to the right hand side - not what you want - so to change it click and hold on the header of the Period column and drag it to the top left a blue vertical arrow should be replaced by a blue horizontal one once the column is in the correct place.
That should give you a martix of Customers down the right hand side and date periods across the top with the appropriate Sales in the middle.
If I follow what you're after try to following basic example:
Create a new Pivot Table, select your Customers as the first dimension and Period as the second (you can use the 'Add calculated dimension' option if you need to format a date into a particular style eg monthname(DateField) gives Feb-2011 from 01/02/2011) Then as your expression enter something along the lines of sum(Sales) and click OK. This will create a Pivot with 2 dimensions to the right hand side - not what you want - so to change it click and hold on the header of the Period column and drag it to the top left a blue vertical arrow should be replaced by a blue horizontal one once the column is in the correct place.
That should give you a martix of Customers down the right hand side and date periods across the top with the appropriate Sales in the middle.
Perfect - this is exactly what I needed! Do you know if it is possible to have a grouped expression so I can switch between sales amounts and quantities, for instance? I tried to do it but it just shows the first expression in the chart.
In those situations I tend to simply use 2 expressions and have them sat side by side in the Pivot table - you may have to tweak the alyout a little to display it in a suitable way be it tends to work.
Alternatively you can create 2 charts and simply switch between them (use the 'Auto Minimize function on the Caption tab of the charts property boxes) - you could also control this with a conditional show function.
Personally I've never used Grouped Expressions - that may be my oversight but I've never had cause to.
To create a Group expression, all you have to do is write all the expressions you want to group first and then Drag and Drop one on top of the other once the creation is done.. It will create a Gorup for you and would show in the chart.
