Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kash04kk
New Contributor II

Count between dates

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

1 Reply

Re: Count between dates

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

Capture.PNG

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

Community Browser