Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Mamta123
Contributor II
Contributor II

Date and time in Qliksense

I have date column in the format given below:  

1/1/2019 4:34:00 AM

From the data extracted, I need to count the number of tickets created between 4.00 am Last day and 4.00 PM today.

I need to include time and date in the calculation.The calculation is used in one of the visualization.

1 Solution

Accepted Solutions
Vegar
MVP
MVP

Does your TestData turn out correct? If so then you could try this.

 


DayName(Timestamp#([ProcessCreationDate], 'M/D/YYYY hh:mm'),0, time#(4, 'h')) as [BusinessDay]

View solution in original post

4 Replies
Vegar
MVP
MVP

I would consider introducing a new business day field (consider it as date version of fiscal year).

 

Take a look at my script and the output shown in picture below.

SET TimestampFormat='M/D/YYYY hh:mm:ss TT';

Output:
LOAD 
  DayName(RecordedTime,0, time#(4, 'h') ) as BusinessDay,
  DayName(RecordedTime) as CalendarDay,
  RecordedTime
inline [
RecordedTime
1/1/2019 4:34:00 AM
2/1/2019 4:40:00 AM
2/1/2019 3:34:00 AM
3/1/2019 3:34:00 AM
3/1/2019 06:37:00 PM
4/1/2019 01:37:00 AM
4/1/2019 03:37:00 AM
4/1/2019 08:37:00 PM
4/1/2019 11:37:00 PM
4/1/2019 07:37:00 AM
];

image.png

Mamta123
Contributor II
Contributor II
Author

The BusinessDay is not showing up

I have used the code: 

Timestamp(Timestamp#([LastUpdated], 'M/D/YYYY hh:mm') ) AS [LU],
Timestamp(Timestamp#([ProcessCreationDate], 'M/D/YYYY hh:mm') ) AS [TestData],
DayName(TestData,0, time#(4, 'h')) as [BusinessDay]

Scrn 3.png

Vegar
MVP
MVP

Does your TestData turn out correct? If so then you could try this.

 


DayName(Timestamp#([ProcessCreationDate], 'M/D/YYYY hh:mm'),0, time#(4, 'h')) as [BusinessDay]

Mamta123
Contributor II
Contributor II
Author

 Thank you Vegar !! It was great help 🙂