Refer to a previous row in a Pivot Table

[pauljohansson]

Hi,

I want to compare a month with its previous months. For example, i want to compare ON_VIPPOTENTIAL (15)  2014-Oct with ON_VIPPOTENTIAL (12)  2014-Sep.I want to be able to do this without selecting a specific month. Any ideas or pointers are welcome.

Thanks in advance,

br

Paul

YearMonth	SegmentName	#Value	%
2014-Sep	ON_NDC	274
	ON_NRC	693
	ON_VIPPOTENTIAL	12
	UNKNOWN	1050
2014-Oct	ACT_KEEP	13
	ACT_NORMAL	229
	ACT_WINNER	75
	ACT_VIP	1
	ON_NDC	406
	ON_NRC	637
	ON_VIPPOTENTIAL	15
	SI_BIGWINNER	1
	SI_NEVERPLAYED	523
	SI_NORMAL	379
	SI_WINNER	23
	UNKNOWN	280

[crusader_]

Hi Paul,

You need to read more about Chart Functions Inter Record.

For Your example with Pivot table look at before() and after() functions.

Hope this helps you.

Regards,

Andrei

[Mark_Little]

Hi Paul,

I am not sure of a way of doing this in the Pivot, But you could look at making a Previous column on loading the table.

Something like (in the script)

Previous(ON_VIPPOTENTIAL)  AS PreviousON_VIPPOTENTIAL,

Then on the chart you can use SUM(ON_VIPPOTENTIAL) - SUM(PreviousON_VIPPOTENTIAL)?

hope this helps?

Mark

[miguelbraga]

Hi Paul,

The first column [#Value] should be something like this:

=sum({$<SegmentName = , Year = {$(=Max(Year))}, MonthNumber = {"$(=max({<Ano={$(=max(Year))}>} MonthNumber))"} >} Value)

The second column (previous month) should be something like this:

=sum({$<SegmentName = , Year = {$(=Year(addmonths(max(Date),-1)))}, MonthNumber = {{$(=(month(addmonths(max(date($(=Max(Year)) & '-' & num($(=max({<Year={$(=max(Year))}>} MonthNumber)),'00') & '-' & '01', 'YYYY-MM-DD')),-1))))} >} Value)

You can add a third column with this expression toi get the variation of both months like this:

=(Column(2)-Column(3))/Column(3)

You can also check some papers like this:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

YTQ, QTD, MTD and WTD

Previous YTQ, QTD, MTD and WTD

Hope this information help you

Regards,

MB

[swuehl · Accepted solution]

If your YearMonth values show a chronological load order and in your pivot table with dimensions SegmentName and YearMonth, you can try this to calculate the percentage change

=Aggr( Sum(Value)/Above(SumValue)-1 ,SegmentName, YearMonth)

where Sum(Value) is just an example for your expression.