Calculated dimesion in Pivot

[#1]

Hi,

I am creating a pivot table using columns 'Year' and expression 'Sum(Sales)'. Year has values (2007,2008,2009). I just need to show 2007 and 2009 sales so pivot will look like:

2007 2008

Sum(Sales) 1300 2000

I cannot apply a where condition in Script as in other pivot i need to show 2009 data also.

So need to create a calculated dimension on files 'Year' to show sales data for 2007 and 2008.

Please help

Thanks,

Sunil

[avastani]

why not use Set Analysis for all years before current year?

[#3]

Set Analysis will apply on expressions, not on dimension variable.

[avastani]

true that, then how about you create a duplication year in your calendar table and put a null for current year and a the year value otherwise.

then use that as your dimension and suppress where values are null.

[Anonymous]

Why worry about the dimension?

If you use set analysis on the expression to only calculate results for 2007 and 2009:

Sum({$<Year={2007,2009}>} Sales)

Since there is no result for Year=2008 it will not show by default in the pivot since Suppress Zero-Values is checked. Of course you don't have to hard code the years either in the expression but can use Year(Today()) and addmonths(Year(Today()),-24) or something else that makes it dynamic.