topic Re: Static Count in QlikView

Static Count

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

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

Re: Static Count

sunny_talwar — Thu, 29 Dec 2016 20:19:55 GMT

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;

Re: Static Count

vinieme12 — Thu, 29 Dec 2016 20:41:13 GMT

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

Re: Static Count

Anonymous — Thu, 29 Dec 2016 20:51:38 GMT

Can I combine the two expressions into one?

Re: Static Count

johnw — Thu, 29 Dec 2016 22:34:34 GMT

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)

Re: Static Count

vinieme12 — Fri, 30 Dec 2016 07:04:56 GMT

For the TOTAL you mean?