Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Enjoying learning about QLIK Sense and what it can do.
I have a data set of 8,000 unique members with differing contract start and end periods. An example of 1 record follows:
Member ID Date Created Date Ended
902314252 27 Feb 2015 31 Mar 2017
The intent is to produce a trend chart showing a total count of all members for the end of each month where the member contracts were 'active'. Here a contract is considered active if the end of the month is between the two dates inclusive.
Look forward to understanding more about how to deal with time intervals and your thoughts on how to solve this issue.
Thanks,
Ozzie
Glad to help.
Don't forget to close the thread then by makring the correct answer as so.
Hi Omar,
I've adapted your script solution in this thread to load an Excel file of existing data but it creates a synthetic key. Are you able advise how to remove the synthetic key in the script please?
The script in its current form is:
Let varMinDate=num(MakeDate(2016,01,01));
Let varMaxDate=Today();
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
date( MonthEnd(TempDate)-1) as MonthEnd,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
table:
LOAD
Member_ID as MemberID,
"Date Commenced" as created,
"Cessation Date" as ended
FROM [lib://QSE Data/Volunteer Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
IntervalMatch (MonthEnd)
Load distinct created, ended resident table;
Can anyone else provide advice here?
Thanks,
Ozzie