Discussion Board for collaboration on QlikView Scripting.
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.
This may not be the best approach, but I get you the output you are looking for using IntervalMatch:
LOAD [Sr. No.],
(ooxml, embedded labels, table is Sheet1);
LOAD Min([start date]) as minDate,
Max([end date]) as maxDate
LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
LOAD Date($(vMinDate) + IterNo() - 1) as Date
1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
LOAD Distinct [start date], [end date]
Dimension: =If(Day(Date) = 15, Date)
Expression: =Count([unique id])
Attaching the qvw for your reference