Discussion Board for collaboration on QlikView Layout & Visualizations.
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.
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
Year(Date)&month(Date) as YearMonthKey
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
//code to figure out the max(year) in your data set
max(year(Date)) as MaxYear
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
Year(Date) as xAxisDimension,
Year(Date) as xAxisSortNumber
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
text(Month(Date)) as xAxisDimension,
Year(Date)+Month(Date) as xAxisSortNumber
where Year(Date) = $(vMaxYear);
//load rest of your data
LOAD [Customer ID],
Thank You I will try that and let you know how it goes.