# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for
Did you mean:
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.

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
1 Solution

Accepted Solutions
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.

4 Replies
Partner - Specialist

Hi Paul,

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

Hope this helps you.

Regards,

Andrei

Partner - Specialist III

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

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