topic Re: Refer to a previous row in a Pivot Table in QlikView

Refer to a previous row in a Pivot Table

pauljohansson — Mon, 02 Nov 2015 14:23:35 GMT

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,

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

Re: Refer to a previous row in a Pivot Table

crusader_ — Mon, 02 Nov 2015 14:42:13 GMT

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

Re: Refer to a previous row in a Pivot Table

Mark_Little — Mon, 02 Nov 2015 14:43:16 GMT

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

Re: Refer to a previous row in a Pivot Table

miguelbraga — Mon, 02 Nov 2015 17:50:21 GMT

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,

Re: Refer to a previous row in a Pivot Table

swuehl — Mon, 02 Nov 2015 17:57:32 GMT

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.