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

[sunny_talwar]

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

[Anonymous]

Hi Sunny,

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

Kind regards,

Ozzie

[OmarBenSalem]

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:

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,

    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

[Anonymous]

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

[OmarBenSalem]

Already attached ;

[Anonymous]

‌thanks kindly Omar.

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

Ozzie

[Anonymous]

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

[OmarBenSalem · Accepted solution]

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:

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 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

[Anonymous]

‌Hi Omar,

Perfect, you have solved my problem.

thank you kindly,

Ozzie.