Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
Thank You I will try that and let you know how it goes.