How can I exclude a specific selection in one sheet

[Anonymous]

Hi guys,

I have an application with four sheets with a  "Year" table box in the first three sheets (I set it up as "Always One Selected Value") . Users can select whichever year they want to look into.

Now I have problem with the fourth sheet. I plan to include a table with 5 different years' info in different columns regardless the year selection. (eg: as below)

Agent	2009	2010	2011	2012	2013
AG	12	13	164	8964	9731
EG	23	24	546	16856	20166

I also plan to create a chart showing the trend of the 5 years. (The "Year" is one of the dimensions). The Year selection carries over to the fourth sheet...How can I break it please?

[alexandros17]

1) You can do with alternate states

2) you can write in expression (if year is a dimension) Sum({total <year> value)

let me know

[stigchel]

I personally like set analysis over alternate states, so would add the following set expression to all expressions where you want to ignore the selection of Year:

{<Year=>}

e.g.

Sum({<Year=>} Sales)

[Anonymous]

Thanks, Alessandro. I like to try the second way first. What if I have other conditions than ignoring the year selection? The set analysis is like sum({<Condition1, Condition 2>}value). I am not sure in this case where I can put the "total" at.

Thanks a lot!

[Anonymous]

Hi Piet,

Thanks for your opinion. I added the year condition into my set analysis. Now it is ignoring the Year selection but it is only showing the year of 2010....:(

How can I get the 5 years in stead of just one year..?

Thanks a lot!!

[stigchel]

You say the year selection is now ignored but it still shows only 2010 ?? I then assume 2010 is not the year selected in the other sheets?? Without further knowledge of your document I cannot answer that. I would suggest you would check all current selection for maybe some other selections that may be limiting the data display to 2010. If so add them to the set expression like e.g. this

Sum({<Year=,YearWeek=>} Sales)

[beunderf]

Let's assume your year field is named Year, your expression would be:

SUM( {$<Year= >} Value)

Does this help?

[Anonymous]

Thanks, Piet. You are right, 2010 is not the year selected in the other sheets and "year" was the only selection...

[Anonymous]

Hi Frank,

It still shows only one year, 2010, doesn't matter what year I selected in the other sheets. And "Year" was the only selection.

[beunderf]

See attached Example. Is this what you are looking for? Or do I understand your question not correctly?