Dimension level difference for expression in pivot table

[#1]

Hi all,

I've spent many hours trying to find solution to following:

I have a pivot table with 2 dimensions and 2 Expressions. The dimensions are Dept and Week where Week is simply current week number for current year and current week number for last year. (Ex Week will have two values 201220 and 201120, this dimension is calculated using WeekSeq where current weekid 201220 has seq -1 and 201120 has seq -53 since there are 52 weeks in a year). Now I added two expressions to calculate Sales units and Sales dollars.

I would like to add a column in the pivot table that is the difference between 201120 and 201220. So infact I want the table to look something like: Wehere difference is 201220 - 201120.

Dept	Week	201120	201220	Difference
BOYS	Sales Units	30146	27638
	SALES $	283244.3	257467
FAMILY UNDER	Sales Units	38	1
	SALES $	317.02	1
GIRLS	Sales Units	10901	28264
	SALES $	133961.51	266853
INFANT	Sales Units	15464	12709
	SALES $	169133.51	147539
LADIES OUT	Sales Units	-	425
	SALES $	-	6879
LADIES SLEEP	Sales Units	11627	5311
	SALES $	151740.51	71732
LADIES	Sales Units	31489	15174
	SALES $	278691.39	147576
MENS	Sales Units	102960	59695
	SALES $	904260.24	542765

[#2]

Hi,

I have only used your table. Maybe you can uns the attached file for your data.

Regards
Vicky

[#3]

Vicky,

Thanks for the reply. Your solution works however I don't think its the most optimal solution since the data set above was a very simplified version. I was looking for a method to use either pivot table or straight table to produce same result.

Basically show a column with differences just as you would show totals.

Thanks,

Grif