Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raZor
Contributor III
Contributor III

Change Dates and time according to IF condition

Hi Guys,

 

i have a scenario where i want to change the date values according to the IF condition.

 

eg. i have a date field having date of request.

i want to change the date if the request is made on Friday after 5:00 PM to Monday 8:00AM 

eg. if the request comes after 5:00PM 2/08/2019(Friday) then the request date should change to 8:00AM 05/08/2019  (Monday)

Labels (3)
1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

Try as below

if(WeekDay(Date)>=6 , Date + (8-Weekday(Date)) + MakeTime(8,0,0)
,if(WeekDay(Date)=5 and hour(Date) < 17 ,Date + (8-Weekday(Date)) + MakeTime(8,0,0), Date))

View solution in original post

5 Replies
raZor
Contributor III
Contributor III

clipboard_image_0.pngi have tried this expression:-

=Date(If(WeekDay(date) = 'Fri' , If(Time(date) > '4:00:00 PM', date + 3, date)))

 

its working fine with day but not including time.

the blue marked date is having time less than 5:00:00 PM so, that should be same

but rest of the date which is on Friday and time is more than 5:00:00 PM should change to Monday 8:00:00 AM.

 

Regards

sagarjagga
Creator
Creator

For Time you can use like
If(WeekDay(date) = 'Fri' , If(Time(date) > '4:00:00 PM', Date(date + 3,'8:00:00AM MM/DD/YYYY'), Date(date,'hh:mm:ssTT MM/DD/YYYY')))
raZor
Contributor III
Contributor III

i used this -

=If(WeekDay(date) = 'Fri' , If(Time(date) > '4:00:00PM', Date(date + 3,'DD/MM/YYYY 8:00:00 AM'), Date(date,'DD/MM/YYYY h:mm:ss TT')))

 

clipboard_image_1.png

1>here you can see the first date blue is correct but the time is the same as Friday's time

2>other entries are blank

3>there is 'A8' instead of AM

 

can you help?

 

Regards,

 

sagarjagga
Creator
Creator

your requirement is like if the date is between friday 4 pm to monday 8am then you need to display monday 8am date else that date
Try below code , it worked fine for me -
=if(weekday(date)='Sat' or weekday(date)='Sun' or (Weekday(date)='Fri' and time(date,'hh:mm:ss TT')> '4:00:00 PM')
or (Weekday(date)='Mon' and time(date,'hh:mm:ss TT')< '8:00:00 AM') , Replace(Date(date + match(weekday(date),'Sun','Sat','Fri'),'8:00A! DD/MM/YYYY),'!',M), Date(date,'hh:mmTT DD/MM/YYYY'))

Bit lengthy but will work fine. May be, while typing i could have missed some bracket so adjust them.

vinieme12
Champion II
Champion II

Try as below

if(WeekDay(Date)>=6 , Date + (8-Weekday(Date)) + MakeTime(8,0,0)
,if(WeekDay(Date)=5 and hour(Date) < 17 ,Date + (8-Weekday(Date)) + MakeTime(8,0,0), Date))

View solution in original post