Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
pauljohansson
Creator III
Creator 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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

4 Replies
crusader_
Partner - Specialist
Partner - Specialist

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
Luminary
Luminary

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
Partner - Specialist III
Partner - Specialist III

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
MVP
MVP

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.