Discussion board where members can get started with Qlik Sense.
I'm not sure if I'm asking this correctly, but this is essentially what I want to do in a nutshell.
I have data that I'm loading that has a begin date and an end date. so, I want to duplicate the data so that I can chart it by month and by year. for example...
jane doe starts employment march 1, 2017 and ends employment August 31 2017. I want to chart everyone that it's employed for the next 12 months. so, for feb, she would be a no. for march she would be a yes, for april she would be a yes, and so forth..
the data currently looks like this.
Name Start Date End Date
Jane Doe 3/1/2017 8/31/2017
John Doe 1/1/2017 1/1/2020
and I want it to look like this ...
Name Start Date End Date MonthStatus Month
Jane Doe 3/1/2017 8/31/2017 0 Feb
Jane Doe 3/1/2017 8/31/2017 1 March
Jane Doe 3/1/2017 8/31/2017 1 April
John Doe 1/1/2017 1/1/2020 1 Feb
John Doe 1/1/2017 1/1/2020 1 March
I will be updating the data monthly, so in the load, I would have a variable for current month and I would want to load current month + 11...
I also want to do the same routine for Fiscal year, but I'm sure it'll be the same code...except instead of MonthStatus, it would be YearStatus and Month would be FY.
You could create a cartesian product by making a JOIN against all the employees in a subset (start date in present month and the following 11) with 12 values of months:
I attach you a sample, hope it serves...
LOAD * INLINE [
Name2, Start Date2, End Date2
Jane Doe, 3/1/2017, 8/31/2017
John Doe, 1/1/2017, 1/1/2020];
LET vsMonthStart = 201701;
LEFT JOIN (T2)
Month(Month_Num2) AS Month2;
LOAD Date(MakeDate(Year(AddMonths(Date#('$(vsMonthStart)', 'YYYYMM'), Recno() - 1)),
Month(AddMonths(Date#('$(vsMonthStart)', 'YYYYMM'), Recno() - 1)),
1), 'M/D/YYYY') AS Month_Num2
LOAD Name2 AS Name,
[Start Date2] AS [Start Date],
[End Date2] AS [End Date],
Month_Num2 AS Month_Num,
Month2 AS Month
WHERE Date#([Start Date2], 'M/D/YYYY') <= Date#(Month_Num2, 'M/D/YYYY');
DROP TABLE T2;
Try something like
Date#([Start Date],'M/D/YYYY') as [Start Date],
Date#([End Date],'M/D/YYYY') as [End Date]
[Name,Start Date,End Date
AddMonths(MonthStart([Start Date]),IterNo()-1) as YearMonth
While AddMonths(MonthStart([Start Date]),IterNo()-1) <= [End Date];
Then you will get all months between the start and end dates in the field YearMonth. See also Creating Reference Dates for Intervals