Combine Total Year with current year months

kstroupe — Thu, 21 May 2015 14:17:35 GMT

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

JonnyPoole — Thu, 21 May 2015 14:37:02 GMT

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);

Re: Combine Total Year with current year months

kstroupe — Thu, 21 May 2015 14:52:06 GMT

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

topic Combine Total Year with current year months in QlikView

Combine Total Year with current year months

Re: Combine Total Year with current year months

Re: Combine Total Year with current year months