Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group durations across multiple weeks By Week

Hi all,

I have a table which contains the downtime of machines. It looks something similar to the below table:

Capture4.PNG

Therefore I have the following situation:

Capture3.PNG

I am trying to find a way such that I can the downtime i.e Duration (Days) by weeks, as following:

Capture2.PNG

Can someone please recommend how this can be achieved?

Thanks!

1 Solution

Accepted Solutions
sunny_talwar

I think I get it now

Script:

SET FirstWeekDay=0;

Table:

LOAD * Inline [

Duration ID, Start Day, End Day, Duration

D001, 16/04/2016, 22/04/2016, 7

D002, 11/04/2016, 29/04/2016, 19

D003, 16/04/2016, 24/04/2016, 9

];

Table:

LOAD [Duration ID],

  Duration,

  [Start Day],

  [End Day],

  Date([Start Day] + IterNo() - 1) as Date,

  Week([Start Day] + IterNo() - 1) as Week,

  WeekDay([Start Day] + IterNo() - 1) as WeekDay

Resident Table

While [Start Day] + IterNo() - 1 <= [End Day];

DROP Table Table;

Capture.PNG

I don't know how you are calculating your Week, but the the idea will remain the same the Week calculation will change.

Best,

Sunny

View solution in original post

3 Replies
sunny_talwar

Would you be able to explain the logic of downtime Duration? I can't seem to find a relations between your input table and the Gantt chart witht he expected output table.

sunny_talwar

I think I get it now

Script:

SET FirstWeekDay=0;

Table:

LOAD * Inline [

Duration ID, Start Day, End Day, Duration

D001, 16/04/2016, 22/04/2016, 7

D002, 11/04/2016, 29/04/2016, 19

D003, 16/04/2016, 24/04/2016, 9

];

Table:

LOAD [Duration ID],

  Duration,

  [Start Day],

  [End Day],

  Date([Start Day] + IterNo() - 1) as Date,

  Week([Start Day] + IterNo() - 1) as Week,

  WeekDay([Start Day] + IterNo() - 1) as WeekDay

Resident Table

While [Start Day] + IterNo() - 1 <= [End Day];

DROP Table Table;

Capture.PNG

I don't know how you are calculating your Week, but the the idea will remain the same the Week calculation will change.

Best,

Sunny

Not applicable
Author

Hi Sunny,

Sorry for the late replay.

I just applied your example in my application and it worked perfectly!

Thanks again for your contribution!