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?

[sunny_talwar · Accepted solution]

Try this:

=Aggr(RangeSum(Sum(Value), -Below(Sum(Value))), Name, Desc, Date)

[swuehl]

Try

=Only(

     Aggr(

           Sum(Value) - Below( Sum(Value) )

     ,NAME, DESC, DATE)

)

If your DATE field values are not in chronological load order, use

The sortable Aggr function is finally here!

[jeevays7]

Hi sunny,

the above yours exp is not working

[sunny_talwar]

It's not working in your actual scenario or the output from the sample is not what you want? As Stefan pointed, for this to work you need the date field to be sorted in ascending order via the load script.... If it isn't then you can use Sorting through Aggr(), but for that you would need to have at least QV12. Do you have QV12? If yes, then you can try this:

=Aggr(RangeSum(Sum(Value), -Below(Sum(Value))), Name, Desc, (Date, (NUMERIC)))

[jeevays7]

Hi Stefan,

Your expression is not working for me.

[jeevays7]

Thank you sunny and stefan for all your reply's.

Now it's working.