2 Replies Latest reply: Mar 22, 2016 8:20 PM by Sunny Talwar RSS

    Creating a Previous Month Sales Dimension

    Ronak Patel

      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!

        • Re: Creating a Previous Month Sales Dimension
          Stefan Wühl

          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

            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