date - SSRS Expression inversed return -
so have expression calculates number of weekdays between 2 dates (excluding these dates). expression seems give opposite results of want , don't know why. here's expression:
=iif(isnothing(fields!end_date.value) or isnothing(fields!report_submitted_date.value) or fields!report_submitted_date.value>fields!end_date.value,"invalid data", (datediff(dateinterval.day,fields!end_date.value,fields!report_submitted_date.value)+1) - (datediff(dateinterval.weekofyear,fields!end_date.value,fields!report_submitted_date.value)*2) - iif(weekday(fields!end_date.value,1) = 1,1,0) - iif(weekday(fields!end_date.value,1) = 7,1,0) - iif(weekday(fields!report_submitted_date.value,1) = 1,1,0) - iif(weekday(fields!report_submitted_date.value,1) = 7,1,0)-2)
so, result expression every situation end date smaller report submitted date text invalid data
, rest #error
or negative numbers.
now know can solve switching true
, false
clause understand why these results switched because far can read:
if end date or report submitted date null
or if end date bigger report submitted date should invalid data
text.
any answers appreciated.
edit 1:
so have changed expression iif switch.
=switch( isnothing(fields!end_date.value),"invalid data", isnothing(fields!report_submitted_date.value),"invalid data", fields!report_submitted_date.value <= fields!end_date.value, "invalid data", true, "good data" )
and gives me results expect, however, if change good data
expression calculate difference of weekdays between 2 dates still #error
. looks when doesn't recognize report submitted date
nothing
. altered expression below:
=switch( isnothing(fields!end_date.value),"invalid data", isnothing(fields!report_submitted_date.value),"invalid data", fields!report_submitted_date.value <= fields!end_date.value, "invalid data", true, ((datediff(dateinterval.day,fields!end_date.value,fields!report_submitted_date.value)+1) - (datediff(dateinterval.weekofyear,fields!end_date.value,fields!report_submitted_date.value)*2) - iif(weekday(fields!end_date.value,1) = 1,1,0) - iif(weekday(fields!end_date.value,1) = 7,1,0) - iif(weekday(fields!report_submitted_date.value,1) = 1,1,0) - iif(weekday(fields!report_submitted_date.value,1) = 7,1,0)-2) )
result:
Comments
Post a Comment