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

Popular posts from this blog

How has firefox/gecko HTML+CSS rendering changed in version 38? -

javascript - Complex json ng-repeat -

jquery - Cloning of rows and columns from the old table into the new with colSpan and rowSpan -