Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]
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
];
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]
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]
Thank you Vegar !! It was great help 🙂