Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count IDs between Months

I need help completing an app as this is the last expression that I need to create but I'm not sure what steps I need to take.

I would like to count Active Members for my dimension Month. For example, see below color code. The End Date Month of 300001 is an indicator that the Member is still active.

An Active member is any member that falls within my Month dimension. I'm guessing I need to create an additioanl calendar to add my dimension but I'm not clear how to do this...

1 Solution

Accepted Solutions
sunny_talwar

Script:

Table:

LOAD Date(MonthStart([Start Date Month], IterNo() - 1), 'YYYYMM') as Date,

  Status,

  Member,

  [Start Date Month],

  [End Date Month]

While MonthStart([Start Date Month], IterNo() - 1) <= If([End Date Month] >= MonthStart(Today()), MonthStart(Today()), [End Date Month]);

LOAD Status,

    Member,

    Date(MonthStart(Date#([Start Date Month], 'YYYYMM')), 'YYYYMM') as [Start Date Month],

    Date(MonthStart(Date#([End Date Month], 'YYYYMM')), 'YYYYMM') as [End Date Month]

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Straight Table:

Dimension:

Date

Expression:

=Count(DISTINCT Member)


Capture.PNG

View solution in original post

6 Replies
sunny_talwar

Would you be able to provide a sample John?

Anonymous
Not applicable
Author

I attached sample data. The Months should read as followed. The months are not in date format. I'm up for changing anything and everthing with the data to get the counts that I'm looking for. I found an example on QV community of kind of what I'm looking for.

Counting "active" records between two dates

sunny_talwar

Is this something you want on the front end or back end would also work?

Anonymous
Not applicable
Author

I don't care as long as I can represent the data on a chart.

sunny_talwar

Script:

Table:

LOAD Date(MonthStart([Start Date Month], IterNo() - 1), 'YYYYMM') as Date,

  Status,

  Member,

  [Start Date Month],

  [End Date Month]

While MonthStart([Start Date Month], IterNo() - 1) <= If([End Date Month] >= MonthStart(Today()), MonthStart(Today()), [End Date Month]);

LOAD Status,

    Member,

    Date(MonthStart(Date#([Start Date Month], 'YYYYMM')), 'YYYYMM') as [Start Date Month],

    Date(MonthStart(Date#([End Date Month], 'YYYYMM')), 'YYYYMM') as [End Date Month]

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Straight Table:

Dimension:

Date

Expression:

=Count(DISTINCT Member)


Capture.PNG

Anonymous
Not applicable
Author

I don't know what the script is doing but it's the results that I need. I'll review the script and try to understand the steps you took.

Thank you Sunny.