Count between dates

[kash04kk]

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

[sunny_talwar]

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