Dual Date Manipulation (Duration Taken)

[akpofureenughwu]

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

[Anil_Babu_Samineni]

Try This

Load *, Time(Inverval([Resolved date] - [created date]),'MM/DD/YYYY hh:mm:ss fff') as Duration

Resident Table;

[sunny_talwar]

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);

[akpofureenughwu]

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

[akpofureenughwu]

Still have issue generating dates with the existing script?

[sunny_talwar]

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;

[akpofureenughwu]

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?

[sunny_talwar]

Looks good to me, is it not working the way you would expect?

[akpofureenughwu]

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