2 Replies Latest reply: May 21, 2015 10:52 AM by Kim Stroupe RSS

    Combine Total Year with current year months

    Kim Stroupe

      Hello,

      I have a requirement to combine previous years with current year months.  Does anyone have an example of this?  I've placed a picture of what the business would like to see.

       

      thanks

      Kim

        • Re: Combine Total Year with current year months
          Jonathan Poole

          I usually handle that in the data model by creating a date table with the custom buckets (Years and Months in the same field so that the chart creating is very straight forward)

           

          -----------

           

          //load your main data. It includes a Date field.  Create a year/month key because we will be including both years and months in the xAxis dimension

           

          Data:

          LOAD

            [Transaction ID],

              [Salesman ID],

              [Product ID],

              [Serial No],

              [Customer ID],

              [List Price],

              Sales,

              [Gross Margin],

              Date,

              Year(Date)&month(Date) as YearMonthKey

          FROM

          [C:\Qlik Resources\Demos\10 Minute Demo\Data\TransactC.csv]

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

           

          //code to figure out the max(year) in your data set

          MaxYearTable:

          load Distinct

            max(year(Date)) as MaxYear

          Resident Data;


          let vMaxYear=peek('MaxYear',0,'MaxYearTable');

          drop table MaxYearTable;

           

          //Build a table with a new x-axis chart dimension. First load months from previous years. The x-axis values are previous year values

          ChartTable:

          load

            YearMonthKey,

            Year(Date) as xAxisDimension,

            Year(Date) as xAxisSortNumber

          Resident Data

          where Year(Date) < $(vMaxYear);

           

           

          //now add current year months, the x-axis values are months from the current year. add a sort key to ensure the x-axis values are sorted how you want

          Concatenate (ChartTable)

          Load

            YearMonthKey,

            text(Month(Date)) as xAxisDimension,

            Year(Date)+Month(Date) as xAxisSortNumber

          Resident Data

          where  Year(Date) = $(vMaxYear);

           

           

          //load rest of your data

          LOAD Country,

              Region

          FROM

          [C:\Qlik Resources\Demos\10 Minute Demo\Data\REGION.CSV]

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

           

          LOAD [Customer ID],

              Customer,

              Address,

              City,

              Zip,

              Country

          FROM

          [C:\Qlik Resources\Demos\10 Minute Demo\Data\CUSTOMER.CSV]

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);