How get comparison figures?

[#1]

Hi All,

I have the following records.

load * Inline
[
FiscalWeek,Amount,FiscalYear,Region
2010Wk01,100,2010,Brazil
2010Wk02,50,2010,Brazil
2011Wk01,300,2011,Brazil
2011Wk02,150,2011,Brazil

2010Wk01,100,2010,France

2010Wk02,50,2010,France

2011Wk01,300,2011,France

2011Wk02,150,2011,France

]

I use FiscalYear and Region as the parameters and show the numbers in Pivot table.  I put FiscalWeek as dimension, and use expression sum(Amount).

When I choose 2011 as the fiscal year and choose Brazil as the region, the pivot shows the Brazil Amount of 2011Wk01 and 2011Wk02. It works well.

Assume now is 2011Wk02, based on it, I also want to show the Brazil amount of 2010Wk02 in another Pivot table use another expression, this pivot does

not have any dimension, how to achieve it?

I try to use this: sum({<FiscalWeek={'2010Wk02'}>}Amount), but it does not work.

Could you experts help me out?

Thanks.

[sbaldwin]

Hiu, if i understand you correctly you would need another dimension that only has the week number (without the year)  then use some set analysis like this in your second pivot:

=sum({$<FiscalYear = {$(=max(FiscalYear)-1)}> } Amount)

eg attached,

( just reading your post again if you wanted to specify the week specifically you would need to remove the year selection by changing your set expression to

sum({<FiscalYear=, FiscalWeek={'2010Wk02'}>}Amount)

)

Thanks

Steve

[#3]

Thanks, but why this sum({<FiscalWeek={'2010Wk02'}>}Amount) does not work?

[sbaldwin · Accepted solution]

Because you have made a selection of the FiscalYear as you are modifying the current set ($) unless you include that in your set expression it will allways be 0 as 2010Wk02 falls outside the current set (if you were modifying the full set (1) you wouldn't have this issue but then your region selections would also be removed.

Thanks

Steve

[#5]

Many thanks for you explanation, it really helps me a lot.