Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

(Availability) Calendar based on two dates

Hi,

I'm trying to calculate availability for some servers and I'm trying to figure out how to do this based on two dates and duration of the event. This is how my table looks like:

As you can see there is one server (ID 9) that's been down for little more than two months. I want to "split" this value so I can show duration for each month. For this server I would like to able to visualize it like this:

2015-07: xxxx seconds (first_date - 2015-07-31 23:59:59)

2015-08: xxxx seconds (2015-08-01 00:00:00 - 2015-08-31 23:59:59)

2015-09: xxxx seconds (2015-09-01 00:00:00 - end_date)

1 Solution

Accepted Solutions
Henric_Cronström

I wouldn't use Canonical dates for this. Instead I would create reference dates within the intervals. See See Creating Reference Dates for Intervals.

The script could then be

Load *,

  Month(Date) as Month,

  EndTime - StartTime as Duration;

Load *,

  Time(If(first_date>Date,Frac(first_date),0)) as StartTime,

  Time(If(end_date<DayEnd(Date),Frac(end_date),Frac(DayEnd(Date)))) as EndTime;

Load

  ID,

  first_date,

  end_date,

  Date(DayStart(first_date)+IterNo()-1) as Date

From <Source>

While IterNo()<=1 + DayStart(end_date) - DayStart(first_date);

Then you get a field with the duration within each date, so you can create a bar chart with month as dimension and Sum(Duration) as measure.

HIC

View solution in original post

3 Replies
sunny_talwar

Check this out for creating calendar based on two dates: Canonical Date

Henric_Cronström

I wouldn't use Canonical dates for this. Instead I would create reference dates within the intervals. See See Creating Reference Dates for Intervals.

The script could then be

Load *,

  Month(Date) as Month,

  EndTime - StartTime as Duration;

Load *,

  Time(If(first_date>Date,Frac(first_date),0)) as StartTime,

  Time(If(end_date<DayEnd(Date),Frac(end_date),Frac(DayEnd(Date)))) as EndTime;

Load

  ID,

  first_date,

  end_date,

  Date(DayStart(first_date)+IterNo()-1) as Date

From <Source>

While IterNo()<=1 + DayStart(end_date) - DayStart(first_date);

Then you get a field with the duration within each date, so you can create a bar chart with month as dimension and Sum(Duration) as measure.

HIC

Not applicable
Author

Looks great! Thank you!