Summing days late through multiple columns

[chriscardwell06]

I have 4 revised ship date columns, an est ship date, and an actual ship date. I have a column summing days late for est ship date and one summing revised days late. The expression I am having problems with is to calculate the total days late for the revised. I want it to check the 4th revised and if it has a date calculate from that date. If not I want it to go to the 3rd and calculate from that, and so on. I have pasted the code I was trying to use. It just goes all the way to the bottom of the code and calulates from the 1st revised ship date. I have also attached a jpg of the layout of the columns. I also want to do this in the query using expression builder.


Days Late Revised: IIf( [4th Revised Ship Date] <>null,(DateDiff("d", [4th Revised Ship Date] , [Actual Ship Date] )-DateDiff("ww", [4th Revised Ship Date] , [Actual Ship Date] ,1)-DateDiff("ww", [4th Revised Ship Date] , [Actual Ship Date] ,7)),IIf( [3rd Revised Ship Date]<>null ,(DateDiff("d", [3rd Revised Ship Date] , [Actual Ship Date] )-DateDiff("ww", [3rd Revised Ship Date] , [Actual Ship Date] ,1)-DateDiff("ww", [3rd Revised Ship Date] , [Actual Ship Date] ,7)),IIf([2nd Revised Ship Date]<>null,(DateDiff("d", [2nd Revised Ship Date] , [Actual Ship Date] )-DateDiff("ww", [2nd Revised Ship Date] , [Actual Ship Date] ,1)-DateDiff("ww", [2nd Revised Ship Date] , [Actual Ship Date] ,7)),IIf([1st Revised Ship Date]<>null ,(DateDiff("d",[1st Revised Ship Date],[Actual Ship Date])-DateDiff("ww",[1st Revised Ship Date],[Actual Ship Date] ,1)-DateDiff("ww",[1st Revised Ship Date], [Actual Ship Date] ,7)),0)

 

[CJ_London]

would be a awfully lot easier if your data was normalised - what happens if you have a 5th revised date?

and in your example, what happened to the 2nd and 3rd revised date?

but the way I would do it would by to make use of a nested nz function


something like

datediff("ww",nz(4threviseddate,nz(3rdreviseddate,nz(2ndreviseddate,nz(1streviseddate,actualshipdate)))),actualshipdate

)