12 Replies Latest reply: Jul 25, 2017 10:48 AM by Ozzie Boeuf

Count of members across monthly or quarterly time intervals

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

• Re: Count of members across monthly or quarterly time intervals

You can either use IntervalMatch or while Loops in the Script to create either all dates or monthends between your two dates (Date Created and Date Ended). One you do that, you should be very easily be able to use the new date field as the dimension and Count(DISTINCT [Member ID]) as your expression

• Re: Count of members across monthly or quarterly time intervals
Hi Sunny,

Thank you kindly for your response.  I will try your suggestions.

Kind regards,

Ozzie

• Re: Count of members across monthly or quarterly time intervals

Maybe sthing like this?

// First, we created a calendar, and we'll later use the MonthEnd field in our chart.

We'll also use the created Date field to match it with our interval

Let varMinDate=num(MakeDate(2017,01,01));

Let varMaxDate=num(MakeDate(2017,12,31));

TempCalendar:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:

TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

MonthEnd(TempDate) as MonthEnd,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

//This is your table, I just format the 2 fields as Dates

table:

load MemberID, date(Date#(DateCreated, 'DD MMM YYYY')) as created,

date(Date#(DateEnded, 'DD MMM YYYY')) as ended

;

load * Inline [

MemberID,  DateCreated,  DateEnded

1,    27 Feb 2017,  31 Mar 2017

2,    27 Mar 2017,  31 Mar 2017

3,    27 Jan 2017,  31 Mar 2017

3,    27 Sep 2017,  31 Nov 2017

4,    27 Nov 2017,  31 Dec 2017

];

//This is the table to Match between the Date of our calendar and your interval (created and ended) fields.

IntervalMatch (Date)

Load distinct created, ended resident table;

result:

See the attached app for more details

• Re: Count of members across monthly or quarterly time intervals
HI Omar,

Thank you kindly for taking time out to help me here.

If you can attach the app, I'd like to see how you did it.

Thanks again,

Ozzie

• Re: Count of members across monthly or quarterly time intervals

thanks kindly Omar.

I Couldn't see it in the Jive app but I can now in browser.

Ozzie

• Re: Count of members across monthly or quarterly time intervals

Hi Omar,

That's brilliant.  One question to help me understand and seek your guidance:

Member ID 3 in your example shows a Date Ended of 01 Dec 2017.  If the Date Ended is not >= the Month End date, it is to be excluded from the count for that month.  So therefore based on this example, because the contract ended on 1 Dec 2017 which is before that month's End date (i.e. 31 Dec 2017), it is not to be included in the December 2017 month's count.

What do I need to change in your script to achieve this?

Thanks so kindly,

Ozzie

• Re: Count of members across monthly or quarterly time intervals

In that case, do the matching with the MonthEnd field instead of the Date field

Test with this script :

Let varMinDate=num(MakeDate(2017,01,01));

Let varMaxDate=num(MakeDate(2017,12,31));

TempCalendar:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:

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 MemberID, date(Date#(DateCreated, 'DD MMM YYYY')) as created,

date(Date#(DateEnded, 'DD MMM YYYY')) as ended

;

load * Inline [

MemberID,  DateCreated,  DateEnded

1,    27 Feb 2017,  31 Mar 2017

2,    27 Feb 2017,  31 Mar 2017

3,    27 Jan 2017,  31 Mar 2017

3,    27 Sep 2017,  31 Nov 2017

4,    27 Nov 2017,  31 Dec 2017

5,    27 Dec 2017,  29 Dec 2017

];

IntervalMatch (MonthEnd)

Load distinct created, ended resident table;

result :

See the attached app

• Re: Count of members across monthly or quarterly time intervals

Hi Omar,

Perfect, you have solved my problem.

thank you kindly,

Ozzie.

• Re: Count of members across monthly or quarterly time intervals

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

• Re: Count of members across monthly or quarterly time intervals

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:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:

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:

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;

• Re: Count of members across monthly or quarterly time intervals

Can anyone else provide advice here?

Thanks,

Ozzie