specific pivot feature

[Anonymous]

hello everybody.

i have this data set: shops and their sales per year. i'd like to create a pivot chart showing the sales per shop/year AND the differences (absolute and percent) between consecutive years. see the followng image: image (Excel example):

thanx

[luciancotea]

2012             =SUM( {<Year={2012}> Sales}

2012-2011     =SUM( {<Year={2012}> Sales} - SUM( {<Year={2011}> Sales}

2012-2011%  =NUM( SUM( {<Year={2012}> Sales} / SUM( {<Year={2011}> Sales} - 1, '#,##0%')

.

.

.

you get the picture...

[Anonymous]

i shouldn't insert an expression for each year, it's meant to be dynamic and automatically do all the calculations for each year found in the data set. is it possible?

[luciancotea]

Yes, it's possible to do that. It will not look exactly like that, but I don't think it's an issue.

Add "Year" as dimension (drag it horizontally after) and create 3 expressions: Sales, Diff and Diff [%]

Sales: =SUM( Sales )

Diff: = this takes some time and I don't have it right now

Diff [%]:

You will get:

	2012	2012	2012	2011	2011	2011
Store	Sales	Diff	Diff [%]	Sales	Diff	Diff [%]
Store 1
Store 2
Store 3
Store 4

[jvishnuram]

Hi Car,

The output you are looking for is quite possible and also it can be achievable as a dynamic one.

2012 (=Max(Year))            =SUM( {<Year={"=Max(Year)"}> Sales}

2012-2011 (= (Max(Year)-(Max(Year)-1) ) )     =SUM( {<Year={"=Max(Year)"}> Sales} - SUM( {<Year={"=Max(Year)-1"}> Sales}

2012-2011% (=(Max(Year)-(Max(Year)-1) ))  % =NUM( SUM( {<Year={"=Max(Year)"}> Sales} / SUM( {<Year={"=Max(Year)-1"}> Sales} - 1, '#,##0%')

[Anonymous]

my Year dimension already is in a numeric format: is the syntax of the expressions you suggest ready for that?

[Anonymous]

UP! (???)

[#8]

Please find below an application that try to solve your problem.

Sales columns have to be expression as you can not use your year dimension in the pivot table.

Please tell me if it is usefull or not.

Regards

Stéphane

[Anonymous]

very useful. now i just should get it dynamic, inserting each year from earliest to latest automatically in the pivot chart

[#10]

please find below the application with dynamic set analysis

regards

stephane