Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
pauljohansson
Contributor III

Refer to a previous row in a Pivot Table

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-SepON_NDC274
ON_NRC693
ON_VIPPOTENTIAL12
UNKNOWN1050
2014-OctACT_KEEP13
ACT_NORMAL229
ACT_WINNER75
ACT_VIP1
ON_NDC406
ON_NRC637
ON_VIPPOTENTIAL15
SI_BIGWINNER1
SI_NEVERPLAYED523
SI_NORMAL379
SI_WINNER23
UNKNOWN280

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Refer to a previous row in a Pivot Table

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.

4 Replies
Partner
Partner

Re: Refer to a previous row in a Pivot Table

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

Partner
Partner

Re: Refer to a previous row in a Pivot Table

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
Valued Contributor III

Re: Refer to a previous row in a Pivot Table

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

MVP
MVP

Re: Refer to a previous row in a Pivot Table

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.