Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day everyone,
I have a unique challenge on dual date
In the attached file, I have two dates: date createdand resolved date.
I don't know how to manipulate these dates and I need to calculate the duration taken for an process (item)
Resolved date - created date = duration.
Thanks
Try This
Load *, Time(Inverval([Resolved date] - [created date]),'MM/DD/YYYY hh:mm:ss fff') as Duration
Resident Table;
May be like this?
Table:
LOAD Id,
[Affected User],
Priority,
Urgency,
[Affected Service],
Area,
Source,
[Affected CI],
Title,
[Created Date],
[Created By],
[Required By],
Department,
Status,
[Support Group],
[Assigned To],
[Implementation Results],
Notes,
[Completed Date & Time],
Location,
Interval([Completed Date & Time] - [Created By], 'D h:mm:ss') as Duration
FROM
duration.xlsx
(ooxml, embedded labels, table is Sheet1);
Thanks Sunny, How do I combine this script with master calendar
I want to drill down from Year, Quater, Month, Weeks even day
Thanks
I tried the attached script but no data displayed
Still have issue generating dates with the existing script?
Which date you want to link your master calendar to? and what is gas table? I am not sure I understand what table are you using to create your master calendar?
Temp:
LOAD Min(Date) as minDate,
Max(Date) as maxDate
Resident gas;
Hello Sunny, The gas table is a table is a mistake, though to be Table.
Like I told you earlier, its my first time handling this kinda of data.
Please I need your suggestion, Which date should I link to the master calendar?
I used the created date since I would love to calculate each process duration.
I edited the script to this:
Table:
LOAD Id,
[Affected User],
Priority,
Urgency,
[Affected Service],
Area,
Source,
[Affected CI],
Title,
[Created Date],
[Created By] as Date,
[Required By],
Department,
Status,
[Support Group],
[Assigned To],
[Implementation Results],
Notes,
[Completed Date & Time],
Location,
Interval([Completed Date & Time] - [Created By], 'D h:mm:ss') as Duration
second table
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident Table;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
How about this?
Looks good to me, is it not working the way you would expect?
Hello everyone
... It looks perfect but I am having a challenge here.
The date contain timestamp.. I was able to you just the date only using the floor function.
BUt the format of the duration is not in the desired format esp with the - sign,
Please see the attached for clarity
Thanks