Count of members across monthly or quarterly time intervals

[Anonymous]

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

[OmarBenSalem]

Glad to help.

Don't forget to close the thread then by makring the correct answer as so.

[Anonymous]

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;

[Anonymous]

Can anyone else provide advice here?

Thanks,

Ozzie