Pivot table problem, reduce the X and Y axes depending on my selection...pls help

[#1]

Hi all!

I have searched and got no information for my problem...

I have the following issue:

I created a pivot table X-years from field 1 table A, Y-years from field 2 table B.

YEAR	2006	2007	2008	2009	2010	2011	2012
2012	500	3003	8000	600	700	-	50
2011	-	2025	-	1400	1400	800
2010	-	222	-	-	-
2009	-	-	800	-
2008	2565	-	2500
2007	1122	556
2006	1000

and a list box
 

YEAR
2012
2011
2010
2009
2008
2007
2006

OK. What I would like to do is to make a connection between X axes-Yers and Y axes-Years so, after I select one year from my list box in my table to remain only the 6 years in the past depending on my selection.

For example if i select YEAR 2012 my table must be smaller with 1 collumn for X and 1 for Y axes, and to display only the Years 2012,2011,2010,2009,2008,2007 on both axes,  with their values....in one word, to make the 2006 year dissapear...

every information will be very usefull.

Thak you,

Silv.

[Oleg_Troyansky]

You can use Set Analysis to limit Year1 and Year2 to the list of possible values of the field Year:

{<List1=P(Year), List2=P(Year)>}

[#3]

thank you...

But where I should put the condition for using Set Analysis?

And another question, P(Year) should refer to my Years from the list box?

Yhank you.

[Oleg_Troyansky]

Place the Set Analysis condition within your aggregation function:

For example:

sum(  {<List1=P(Year), List2=P(Year)>}   Sales)

And yes, Year in this example is the field in the List Box.

best,

Oleg

[#5]

What I have done so far is creating a script table:

LOAD * INLINE [

year_r1, year

2012,2012

2012,2011

2012,2010

2012,2009

2012,2008

2012,2007

2011,2011

2011,2010

2011,2009

2011,2008

2011,2007

2011,2006

2010,2010

2010,2009

2010,2008

2010,2007

2010,2006

2010,2005

2009,2009

2009,2008

2009,2007

2009,2006

2009,2005

2009,2004

];

where year_r are my years from list box, and year is the year from X-axes...so when selecting one year_r, my table must show the values from the past 6 years...

i had to make the same table for Y-axes years, but when loading this 2 tables, it gives me some garbage...

I hope that I made myself understood

[#6]

can anyone give me an alternative to my solution please!

...all I whant is to exclude the 7-th year in the past from my table (X and Y axes) depending on my selected year.

thank you.