topic Re: Creating a Previous Month Sales Dimension in App Development

Creating a Previous Month Sales Dimension

Tue, 22 Mar 2016 21:56:46 GMT

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!

Re: Creating a Previous Month Sales Dimension

swuehl — Tue, 22 Mar 2016 23:31:22 GMT

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

Re: Creating a Previous Month Sales Dimension

sunny_talwar — Wed, 23 Mar 2016 00:20:02 GMT

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)