[meibrah]

Hi,

Imagine I have some monthly data about some different issues from 2010 and then I have the same data from 2011. What I want to do is a line chart that would show me month by month values from both years so that I'd know in 2010 our issue #1 had an amount of 10 and in 2011 the amount was 20. And when I put 12 months together that would show me the trend of that issue for 12 months compared by the year.

So if I put 2010 data in one pivot table and 2011 data in another pivot table how can I show both data in one chart?

Or, I can of course make one pivot table containing 2010 and 2011 information but then the question is basically the same: How can I make the chart I want from that one pivot table.

Unless there's no other way I don't want to copy the data to another sheet by hand and then create a chart and etc every time I want to do this.

Thanks in advance,

Meibrah

 

[meibrah]

I was fortunate enough to find a person to help me in the office and what I want could directly be done from one pivot table which has all the information by using an intermediate table.

So, all your data is in the main pivot table like issue1, issue2 in x month, y month, z month. Then check the attached screenshot to see what's the intermediate table looks like.

And, the formula which creates the number 21 in 2011 + Jan information is as follows:

=GETPIVOTDATA("Year-Month",'Sheet1'!$A$7,"Year-Month",T(CONCATENATE($A6,"-",B$3)),"Issue",T($A$1))

This says to:

• Get the data from the pivot table
• In the worksheet "Sheet1"
• From the cell "$A$7" (which is 21, the 2011 + Jan data in the pivot table)
• Then which data to look for by the phrase "T(CONCATENATE($A6,"-",B$3))" (which makes "2011-1" by concatenating 2011 and 1 as can be seen in the screenshot, and I do this as the dates (therefore the column names) in my pivot table are like this)
• From the rows named "Issue"
• From the row "T($A$1)" (which is "issue 1" as can be seen in the screenshot)

And, if you want to hide the fields where it has no data and showing #N/A you can use like this:

=IF(ISERROR(GETPIVOTDATA("Year-Month",'Sheet1'!$A$7,"Year-Month",T(CONCATENATE($A6,"-",B$3)),"Issue",T($A$1))),"",GETPIVOTDATA("Year-Month",'Sheet1'!$A$7,"Year-Month",T(CONCATENATE($A6,"-",B$3)),"Issue",T($A$1)))

This is a basic if clause saying if the data returns error use "" instead.

Hope this helps to someone else as well.

Meibrah