Check this updated sample file
first create one calendar whos having 2016-2018 dates
then apply logic for removing sat and sun like
where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1')
make it all dates rename as Date and left join your data with calendar dates.
t2:
load
Date(MakeDate(2016)+recNo()-1,'DD/MM/YYYY') as Date
AutoGenerate 365;
Concatenate
load
Date(MakeDate(2017)+recNo()-1,'DD/MM/YYYY') as Date
AutoGenerate 365;
Concatenate
load
Date(MakeDate(2018)+recNo()-1,'DD/MM/YYYY') as Date
AutoGenerate 365;
store t2 into 'C:\Users\Mehul\Downloads\DateField.qvd'(qvd);
drop table t2;
t1:
load *,
WeekDay(Date) as StartDay
where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1');
LOAD ID,
[Start Date] as Date,
'start' as Flag
FROM
(ooxml, embedded labels, table is Sheet2);
Concatenate(t1)
load *,
weekday(Date) as EndDay
where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1');
LOAD ID,
[End Date] as Date,
'End' as Flag
FROM
(ooxml, embedded labels, table is Sheet2);
store t1 into 'C:\Users\Mehul\Downloads\DateField2.qvd'(qvd);
drop table t1;
t3:
LOAD Date,
WeekDay(Date) as StartDay1,
WeekDay(Date) as EndDay1
FROM
(qvd)
where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1');
left join(t3)
LOAD ID,
Date,
Flag,
StartDay,
EndDay
FROM
(qvd);
make the variables
vMinStartDate=Date(min({<Flag={'Start'}>}Date))
vMaxEndDate=Date(max({<Flag={'End'}>}Date))
use the set analysis
=Count(DISTINCT {<Date={">=$(vMinStartDate)<=$(vMaxEndDate)"}>}Date)
