One PivotChart, Two Axes, Two Tables

Question

Table contains series of transactions.

Table columns include: 1. Date of Transaction 2. Amount of Income 3. Claim Name 5. Date Claim Filed 6. Date Claim Closed

Multiple transactions containing income can be associated with a single claim.

The PivotChart graphs the total income each month. How to create second bar chart on second y-axis that charts the number of separate claims filed in each month?

I already know how to create a second y-axis. But when I attempt to graph the total claims filed in a particular month, the Pivot Table and Pivot Chart instead return the dates of each transaction--NOT the number of claims filed in the corresponding month.

I guess what I'm trying to do is combine two separate tables into one pivot chart. Perhaps the second table is just a list of claims and their corresponding file dates. But how to graph that in the same Pivot Chart as the one showing total monthly income from all transactions?

Whole point of this sort of chart would be to attempt to correlate the number of claims filed in one month with the total monthly income in some future month--to see the trend and lag time between filing of claims and monthly income.

Answer 1

You can have a PivotTable with both the sum of transactions and count of transactions. You'll just need to put two fields into the data area of the PivotTable. One needs to be the Amount grouped by Sum, and the other can be any field grouped by Count (assuming every row has a value). The PivotChart will then update automatically.

However, if you're saying you need a count of the number of unique claims each month, then additional work is required because PivotTables can't make unique counts. You would first need to create a separate table that has each claim just once with the total value of all transactions. Then create a PivotTable on that data, again with two fields in the data area of the PivotTable.