Count between dates

kash04kk — Wed, 01 Jul 2015 10:18:44 GMT

Hello everyone.

I want to count members whose start date is before 15th of every month and end date is after 15th of every month.

Please refer to the excel file attached. In sheet 1 is the main data. I want to count 'unique id'. In sheet 2, i've done it using counitfs function. i want to replicate the same in qlikview.

Thanks

Re: Count between dates

sunny_talwar — Wed, 01 Jul 2015 10:31:56 GMT

This may not be the best approach, but I get you the output you are looking for using IntervalMatch:

Script:

Table:

LOAD [Sr. No.],

[unique id],

[start date],

[end date]

FROM

Community_170762.xlsx

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD Min([start date]) as minDate,

Max([end date]) as maxDate

Resident Table;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

Calendar:

LOAD Date($(vMinDate) + IterNo() - 1) as Date

AutoGenerate

1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

IntervalMatch:

IntervalMatch(Date)

LOAD Distinct [start date], [end date]

Resident Table;

Straight Table:

Dimension: =If(Day(Date) = 15, Date)

Expression: =Count([unique id])

Attaching the qvw for your reference

HTH

Best,

Sunny

topic Count between dates in QlikView

Count between dates

Re: Count between dates