topic Re: Count IDs between Months in QlikView

Count IDs between Months

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count IDs between Months

sunny_talwar — Tue, 16 Aug 2016 14:21:32 GMT

Would you be able to provide a sample John?

Re: Count IDs between Months

Anonymous — Tue, 16 Aug 2016 15:19:56 GMT

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

Re: Count IDs between Months

sunny_talwar — Tue, 16 Aug 2016 16:58:21 GMT

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

Re: Count IDs between Months

Anonymous — Tue, 16 Aug 2016 17:21:26 GMT

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

Re: Count IDs between Months

sunny_talwar — Tue, 16 Aug 2016 17:28:51 GMT

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)

Re: Count IDs between Months

Anonymous — Tue, 16 Aug 2016 19:07:59 GMT

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.