topic Re: Dual Date Manipulation (Duration Taken) in QlikView

Dual Date Manipulation (Duration Taken)

akpofureenughwu — Thu, 22 Sep 2016 16:48:17 GMT

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

Re: Dual Date Manipulation (Duration Taken)

Anil_Babu_Samineni — Thu, 22 Sep 2016 16:56:15 GMT

Try This

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

Resident Table;

Re: Dual Date Manipulation (Duration Taken)

sunny_talwar — Thu, 22 Sep 2016 17:00:06 GMT

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

Re: Dual Date Manipulation (Duration Taken)

akpofureenughwu — Thu, 22 Sep 2016 17:20:25 GMT

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

Re: Dual Date Manipulation (Duration Taken)

akpofureenughwu — Thu, 22 Sep 2016 19:15:27 GMT

Still have issue generating dates with the existing script?

Re: Dual Date Manipulation (Duration Taken)

sunny_talwar — Fri, 23 Sep 2016 01:23:22 GMT

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;

Re: Dual Date Manipulation (Duration Taken)

akpofureenughwu — Fri, 23 Sep 2016 02:32:49 GMT

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?

Re: Dual Date Manipulation (Duration Taken)

sunny_talwar — Fri, 23 Sep 2016 07:31:42 GMT

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

Re: Dual Date Manipulation (Duration Taken)

akpofureenughwu — Fri, 23 Sep 2016 19:16:16 GMT

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