Add 1 day to a date then check if its later than today

[Winstodge]

Hi Everyone

I am trying to add 1 day to a date field(PO_DEL_DATE) and then use that new date to check if its later than today. I am using below but it gives me zero.  So if the current PO_DEL_DATE is Friday (22/10/2021) I want to check the following Monday date (25/10/2021)in my calculation. If the date is Tuesday ( 26/10/2021) then I want to use Wednesday (27/10/2021).

I thought the LastWorkDate would allow me to do this, this is for a KPI

sum(if(LastWorkDate(PO_DEL_DATE,2 < date(Today())), 1, 0))

Any suggestions please.

Thank you

Winstodge

[abhijitnalekar]

Hi @Winstodge ,

Create new dimensions at the script level to check particular dates is weekends on weekdays with the below expression.

if(WeekDay(DepartDate)>3,'WeekEnd','WeekDay') as week

if(week ='WeekEnd', Date(PO_DEL_DATE+2,'DD/MM/YYYY'), Date(PO_DEL_DATE+1,'DD/MM/YYYY') ) as New_Date

and your expression in app-level would be like below sum( if(new_date>date(now(),'DD/MM/YYYY'),1,0 ))

Hope you are looking for the same 

[Winstodge]

Hi Abhijit

Thank you for your reply.

Unfortunately I do not have access to the load script or to create a variable.

Is there any other suggestions?

Thanks

Winstodge

[abhijitnalekar]

Hi @Winstodge ,

Check below expression.

sum(
if(week ='WeekEnd', Date(PO_DEL_DATE+2,'DD/MM/YYYY'), Date(PO_DEL_DATE+1,'DD/MM/YYYY') )
if(
if(WeekDay(DepartDate)>5, Date(PO_DEL_DATE+2,'DD/MM/YYYY'), Date(PO_DEL_DATE+1,'DD/MM/YYYY') )
>date(now(),'DD/MM/YYYY'),1,0 )

)

[Winstodge]

Hi Abhijit

Thanks for your reply.

I am getting an error in expression  ')' expected

sum(
if(week ='WeekEnd', Date(PO_DEL_DATE+2,'DD/MM/YYYY'), Date(PO_DEL_DATE+1,'DD/MM/YYYY') )
if(
if(WeekDay(PO_DEL_DATE)>5, Date(PO_DEL_DATE+2,'DD/MM/YYYY'), Date(PO_DEL_DATE+1,'DD/MM/YYYY') )
>date(now(),'DD/MM/YYYY'),1,0 ) )

I tried to correct it by removing one of the if's but I get the same error.

Thanks

[Winstodge]

Its taken me all night but I think I have sorted it with

=sum(if(networkdays(PO_DEL_DATE,Today())<3,0,1))

initial checking it looks good.

Winstodge

[abhijitnalekar · Accepted solution]

Hi @Winstodge ,

Apologies for the wrong expression.

Please check corrected expression 

sum(

if(
if(WeekDay(PO_DEL_DATE)>5, Date(PO_DEL_DATE+2,'DD/MM/YYYY'), Date(PO_DEL_DATE+1,'DD/MM/YYYY') )

>date(now(),'DD/MM/YYYY'),1,0 )

)

[Winstodge]

Thanks Abhijit

I added another factor to capture a saturday

SUM(if(if(WeekDay(PO_DEL_DATE)=5, Date(PO_DEL_DATE+3,'DD/MM/YYYY'), if(WeekDay(PO_DEL_DATE)=6, Date(PO_DEL_DATE+2,'DD/MM/YYYY'), Date(PO_DEL_DATE+1,'DD/MM/YYYY')))
>=date(TODAY(),'DD/MM/YYYY'),0,1 ))

Regards

Winstodge