Creating a Previous Month Sales Dimension

[#1]

Hello,

I have a data set that is a list of projects and their sales on a monthly basis:

Project Code	Reporting Month	Sales
3333	Jan-2015	1000
3333	Feb-2015	1100
3333	Mar-2015	1050
1122	Jan-2015	120
1122	Feb-2015	110
1122	Mar-2015	200

I am trying to figure out how to, either by load script or by a calculated field, create a dimension for previous month sales:

Project Code	Reporting Month	Sales	Previous Month Sales
3333	Jan-2015	1000	-----
3333	Feb-2015	1100	1000
3333	Mar-2015	1050	1100
1122	Jan-2015	120	------
1122	Feb-2015	110	120
1122	Mar-2015	200	110

I have been googling around and looking on the fourms to see if there is a way to do this in QlikSense to no avail.

Is this possible?

Thanks!

[swuehl]

It should be possible, it could be done in the script like (I created a data connection to your above sample table using the web URL community.qlik.com/thread/210552 😞

INPUT:
LOAD
    "Project Code",
    "Reporting Month",
    Sales
FROM [lib://CommTest]
(html, codepage is 1252, embedded labels, table is @1);

LOAD *,
If("Project Code" = Previous("Project Code"), Previous(Sales)) as PrevSales
RESIDENT INPUT
ORDER BY "Project Code", "Reporting Month";

DROP TABLE INPUT;

In your chart object, you can use chart inter record functions like Above(Sales) to access the previous dimension line's Sales value

[sunny_talwar]

Another method:

Table:

LOAD [Project Code],

    MonthName(Date#([Reporting Month], 'MMM-YYYY')) as [Reporting Month],

    Sales

FROM

[https://community.qlik.com/thread/210552]

(html, codepage is 1252, embedded labels, table is @1);

[As-of Table]:

LOAD [Reporting Month] as [As-of Month],

  [Reporting Month],

  'CP' as Flag

Resident Table;

Concatenate ([As-of Table])

LOAD [Reporting Month] as [As-of Month],

  MonthName(AddMonths([Reporting Month], -1)) as [Reporting Month],

  'LP' as Flag

Resident Table;

Straight table

Dimensions

[Project Code]

[As-of Mont]

Expressions:

=Sum({<Flag = {'CP'}>}Sales)

=Sum({<Flag = {'LP'}>}Sales)