Pivot table with Variance

[#1]

Hi all,

I have a data like...

Branch	Month	Sale	Qty
1	Jan-14	5000	40
1	Feb-14	8000	60
1	Mar-14	7000	35
2	Jan-14	4500	20
2	Feb-14	6000	65
2	Mar-14	8700	80

  and I want Pivot Table like

	Sale				Qty
Branch	Jan-14	Feb-14	Mar-14	Variance	Jan-14	Feb-14	Mar-14	Variance
1	5000	8000	7000	-1000	40	60	35	-25
2	4500	6000	8700	2700	20	65	80	15

Here Variance is always Current Month-Previous month (I want Only one Column for Variance)

I do not want in Script side.

Thanks,

Vinay

[MK_QSL]

Difficult to get the same result by Pivot Table but can get the same using Straight Table

Script...

Load

  Branch,

  Date#(Month,'MMM-YY') as Month,

  Sale,

  Qty

Inline

[

Branch, Month, Sale, Qty

1, Jan-14, 5000, 40

1, Feb-14, 8000, 60

1, Mar-14, 7000, 35

2, Jan-14, 4500, 20

2, Feb-14, 6000, 65

2, Mar-14, 8700, 80

];

Create a Straight Table

Dimension = Branch

Expressions

For Sale

Expression = SUM({<Month = {"$(=Date(MonthStart(AddMonths(Today(),-2)),'MMM-YY'))"}>}Sale)

Label  = Date(MonthStart(AddMonths(Today(),-2)),'MMM-YY')

SUM({<Month = {"$(=Date(MonthStart(AddMonths(Today(),-1)),'MMM-YY'))"}>}Sale)

=Date(MonthStart(AddMonths(Today(),-1)),'MMM-YY')

SUM({<Month = {"$(=Date(MonthStart(Today()),'MMM-YY'))"}>}Sale)

=Date(MonthStart(Today()),'MMM-YY')

SUM({<Month = {"$(=Date(MonthStart(Today()),'MMM-YY'))"}>}Sale)-SUM({<Month = {"$(=Date(MonthStart(AddMonths(Today(),-1)),'MMM-YY'))"}>}Sale)

variance

Same you can use for Qty...

[Gysbert_Wassenaar]

If you don't want a script based solution then you cannot use a pivot table. You will have to use a straight table and add separate expressions for each month column and the variance columns.

[#4]

Ok Thanks, But I wud like to do in Pivot table.

Vinay