Pivot table before/after function with two dimensions.

[jeevays7]

Hi all,

I have a pivot table with data as below,

Date	25-Nov-2016	25-Nov-2016	25-Nov-2016	02-Dec-2016	02-Dec-2016	02-Dec-2016
Name	MGT FEES	PIF	PERF FEES	MGT FEES	PIF	PERF FEES
MENA	10.00	20.00	30.00	5.00	10.00	20.00
Raj	1.00	3.00	4.00	5.00	3.00	4.00
Tamil	4.00	6.00	6.00	1.00	2.00	2.00

Now i want the result like below table,

In below table i take the (25-NOV-2016) MGT FEES and subtract with next day(02-Dec-2016) MGT FEES value.

so like that all date values subtracted with previousday values with coressponding Names.

Date	25-Nov-2016	25-Nov-2016	25-Nov-2016	02-Dec-2016	02-Dec-2016	02-Dec-2016
Name	MGT FEES	PIF	PERF FEES	MGT FEES	PIF	PERF FEES
MENA	5.00	10.00	10.00	5.00	10.00	20.00
Raj	-4.00	0.00	0.00	5.00	3.00	4.00
Tamil	3.00	4.00	4.00	1.00	2.00	2.00

How to achieve this in pivot table?

[swuehl]

MGT FEES is an expression, right?

Date is your pivoted dimension and Name dimension shows values like MENA, Raj, Tamil, right?

Have you tried something like

= [YourExpression for MGT FEES] - After( [YourExpression for MGT FEES]  )

Or use Before(), not sure what you want to calculate here.

Replace [YourExpression for MGT FEES] with your expression used, e.g. Sum(Sales)

[sunny_talwar]

I think be better to use RangeSum() so that the next day value doesn't turn null here:

RangeSum([YourExpression for MGT FEES], -After( [YourExpression for MGT FEES]))

[jeevays7]

Hi Sunny and Stefan,

here MGT FEES,PIF and PERF FEES also a dimension(Desc) values.

	Date	25-Nov-2016
Name	Desc	MGT FEE
MENA		10.00
RAJ		1.00

[sunny_talwar]

What is your expression here?

[sunny_talwar]

I think this should still work for you:

RangeSum(ExpressionHere, -After(ExpressionHere))

Replace ExpressionHere with whatever your expression is

[jeevays7]

just sum(values) is my expression

[sunny_talwar]

Can you try this:

RangeSum(Sum(values), -After(Sum(values)))

[jeevays7]

Hi sunny,

Name,Date,Desc is my dimensions and sum(Value) is my expression.

Name dimension is being left side and Date, Desc dimension should be top of the pivot table

[jeevays7]

The pivot table looking like this without design.

	Date		25-Nov-2016			02-Dec-2016
Name	Desc	MGT FEES	PIF	PERF FEES	MGT FEES	PIF	PERF FEES
MENA		10.00	20.00	30.00	5.00	10.00	20.00
Raj		1.00	3.00	4.00	5.00	3.00	4.00
Tamil		4.00	6.00	6.00	1.00	2.00	2.00