Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Creating a Previous Month Sales Dimension

Hello,

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

Project CodeReporting MonthSales
3333Jan-20151000
3333Feb-20151100
3333Mar-20151050
1122Jan-2015120
1122Feb-2015110
1122Mar-2015200

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 CodeReporting MonthSalesPrevious Month Sales
3333Jan-20151000-----
3333Feb-201511001000
3333Mar-201510501100
1122Jan-2015120------
1122Feb-2015110120
1122Mar-2015200110


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!

2 Replies
MVP
MVP

Re: Creating a Previous Month Sales Dimension

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

MVP
MVP

Re: Creating a Previous Month Sales Dimension

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)

Capture.PNG