Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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)