I have been asked by my users to create a pivot table that shows the total values of customers that have migrated from one band to another band between two quarters. Which would look like a table they have which looks like this:
So in this, we have the Sum of the VALUE field contained in the body of the table. The BAND field on the Y axis is quarter 1 only and the BAND field on the X axis is for quarter 2. So the problem here is that the BAND field is one field. All quarter data is in the same table and I have a field which says which quarter a record is from (QUARTER). I can have the user select the two quarters to compare, with input boxes for variables QUARTER_1 and QUARTER_2.
The shaded diagonal shows the sum of the VALUE field for customers where the BAND field is the same for records in QUARTER_1 and QUARTER_2. The values that lie outside this are the sum of the VALUE field for customers where the BAND field has changed over the different quarters.
Does anyone know whether this can be done with a pivot table while keeping with only one BAND field? I can't really make multiple BAND fields as the number of quarters in the data set is not constant.