Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kstroupe
Creator
Creator

Combine Total Year with current year months

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

2 Replies
JonnyPoole
Employee
Employee

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

(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

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

LOAD [Customer ID],

    Customer,

    Address,

    City,

    Zip,

    Country

FROM

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

kstroupe
Creator
Creator
Author

Thank You I will try that and let you know how it goes.