Static Count

[Anonymous]

See attached and below for sample data and app.

I would like to create a static count.

Criteria:

Dimension: StartDate_YYYYMM

Expression/Script:

  I would like to count all "active" from column StatusofMember AND when Statusofmember is closed with an enddate on or after the last day of the StartDate month. I broke it down below but I need help implmenting this into QV.

1	201610	10/1/2016	300001	1/1/3000	Active
2	201610	10/1/2016	201611	11/15/2016	Closed
11	201610	10/15/2016	201611	11/30/2016	Closed
3	201610	10/15/2016	300001	1/1/3000	Active
4	201610	10/1/2016	300001	1/1/3000	Active
10	201610	10/1/2016	300001	1/1/3000	Active
12	201610	10/1/2016	201611	11/30/2016	Closed
13	201611	11/1/2016	300001	1/1/3000	Active
14	201611	11/1/2016	201612	12/1/2016	Closed
15	201612	12/1/2016	300001	1/1/3000	Active

Example	Active	Closed(Count last day of month or after)	Total	Active ID	Closed ID
201610	4	3	7	1,3,4,10	2,11,12
201611	5	3	8	1,3,4,10,13	11,12,14
201612	6	0	6	1,3,4,10,13,15	n/a

[sunny_talwar]

May be using IntervalMatch

Sample:

LOAD ID,

    Date(MonthStart(STARTDATE_YYYYMM), 'YYYYMM') as STARTDATE_YYYYMM,

    StartDate,

    Date(MonthStart(

      If(ENDDATE_YYYYMM = MakeDate(3000, 1), Today(),

      If(STATUSOFMEMBER = 'Closed' and Day(EndDate) = Day(MonthEnd(EndDate)), ENDDATE_YYYYMM, MonthStart(ENDDATE_YYYYMM)-1))), 'YYYYMM') as ENDDATE_YYYYMM_JOIN,

    ENDDATE_YYYYMM,   

    EndDate,

    STATUSOFMEMBER

FROM

[delete.xls]

(biff, embedded labels, table is Sample$);

Temp:

LOAD Date(AddMonths(Min, IterNo() - 1), 'YYYYMM') as MonthYear

While AddMonths(Min, IterNo() - 1) <= Max;

LOAD Min(STARTDATE_YYYYMM) as Min,

  Max(ENDDATE_YYYYMM_JOIN) as Max

Resident Sample;

Left Join (Sample)

IntervalMatch(MonthYear)

LOAD STARTDATE_YYYYMM,

  ENDDATE_YYYYMM_JOIN

Resident Sample;

[vinieme12]

haven't looked at your app, but if you want in Chart then try below

Active

count({<STATUSOFMEMBER={'Active'}>}ID)

Closed

sum( {<STATUSOFMEMBER={'Closed'} >} AGGR( if(EndDate >= MonthEnd(StartDate) ,1),ID))

[Anonymous]

Can I combine the two expressions into one?

[johnw]

Here's a different data model solution, with a separate table of the months that an ID could be considered active according to my understanding of your definition. Hopefully no bugs. Works at least on the sample data.

MonthsActive:
LOAD ID
,date(monthstart(StartDate,iterno()-1),'MMM YYYY') as ActiveMonth
RESIDENT Raw
WHILE daystart(monthend(StartDate,iterno()-1))<=rangemin(EndDate,monthend(today()))
;

Dimension  = ActiveMonth

Expression = count(distinct ID)

[vinieme12]

For the TOTAL you mean?