Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
haneeshmarella
Creator II
Creator II

Add next days data before 5 AM under previous day's day

Hi everyone,

I have the below fields. 

DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY') as MOD3_DATE,
INTERVAL(TIME#(NUM(MOD3_TIME,'000000'),'hhmmss'),'hh:mm:ss') as MOD3_TIME,
TIMESTAMP(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY') + INTERVAL(TIME#(NUM(MOD3_TIME,'000000'),'hhmmss'),'hh:mm:ss')) as MOD3_TIMESTAMP,
WEEK(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as MOD3_WEEK,
WEEKDAY(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as MOD3_WEEKDAY

I need to add the data under previous day's day if it is before 5 AM the next day. For example, if MOD3_WEEKDAY is Thursday and has data from 12 AM to 5 AM, it should show up under Wednesday on filtering. 

Any help is highly appreciated. 🙂 

Labels (5)
1 Solution

Accepted Solutions
sunny_talwar

May be try this

If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY')) as MOD3_DATE, WeekDay(
If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY'))
) as MOD3_WEEKDAY

 

View solution in original post

2 Replies
sunny_talwar

May be try this

If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY')) as MOD3_DATE, WeekDay(
If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY'))
) as MOD3_WEEKDAY

 

haneeshmarella
Creator II
Creator II
Author

Thanks a lot Sunny!