Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

katsafone
New Contributor III

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

1 Solution

Accepted Solutions
omarbensalem
Esteemed Contributor

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:

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 :

Capture.PNG

See the attached app

12 Replies
MVP
MVP

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

katsafone
New Contributor III

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


omarbensalem
Esteemed Contributor

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:

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:

Capture.PNG

See the attached app for more details

katsafone
New Contributor III

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


Highlighted
omarbensalem
Esteemed Contributor

Re: Count of members across monthly or quarterly time intervals

Already attached ;

Capture.PNG

katsafone
New Contributor III

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

katsafone
New Contributor III

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

omarbensalem
Esteemed Contributor

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:

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 :

Capture.PNG

See the attached app

katsafone
New Contributor III

Re: Count of members across monthly or quarterly time intervals

‌Hi Omar,

Perfect, you have solved my problem.

thank you kindly,

Ozzie.