QlikSense expression to find average duration over a period of time

Question

I want to find the overall duration in hours over different periods of time. ie. once I add filters such as 'October' it should show me the overall hours for that month. I want to count duplicate lessons for multiple attendees as 1 lesson. ie. the duration spent to teach the subject.

 Date        Duration  Subject   Attendee
 1/10/2019     2:00    Math       Joe Bloggs
 1/10/2019     2:00    Math       John Doe
 2/10/2019     3:00    English    Jane Doe
 6/11/2019     1:00    Geog       Jane Roe
 17/12/2019    0:30    History    Joe Coggs

I want the overall hours spent on the subjects. This mean the duration total above should add up to 6:30, as the two math lessons should only count as 1 lesson (2 hours). How can I write an expression that produces a KPI of the overall learning ours, and then also allows me to drill down to month and date. Thanks in advance

score 2 · Accepted Answer · answered Oct 06 '19 at 16:34

Can suggest you to create another table that will contains the distinct values (im presuming that the unique combination is Date <-> Subject)

The script below will create OverallDuration table will contains the distinct duration values for the combination Date <-> Subject. This way you will have one additional field OverallDuration which can be used in the KPI.

The OverallDuration table is linked to the RawData table (which is linked itself to the Calendar table) which means that OverallDuration calculation will respect the selections on Subject, LessonYear, LessonMonth etc. (have a look at the Math selection picture below)

RawData:
Load
  *,
  // Create a key field with the combination of Date and Subject
  Date & '_' & Subject as DateSubject_Key
;
Load * Inline [
Date,          Duration, Subject,  Attendee
1/10/2019,     2:00,     Math,     Joe Bloggs
1/10/2019,     2:00,     Math,     John Doe
2/10/2019,     3:00,     English,  Jane Doe
6/11/2019,     1:00,     Geog,     Jane Roe
17/12/2019,    0:30,     History,  Joe Coggs
];

// Load distinct DateSubject_Key and the Duration
// converting the duraion to time.
// This table will link to RawData on the key field
OverallDuration:
Load
  Distinct
  DateSubject_Key,
  time(Duration)          as OverallDuration
Resident
  RawData
;

// Creating calendar table from the dates (distinct) 
// from RawData and creating two additional fields - Month and Year
// This table will link to RawData on Date field
Calendar:
Load
  Distinct
  Date,
  Month(Date)             as LessonMonth,
  Year(Date)              as LessonYear
Resident
  RawData
;

Once the script above is reloaded then your expression will be just sum( OverallDuration ) And you can see the result in the pivot table below:

The overall duration is 06:30 hours and for Math is 02:00 hours:

And your data model will look like this:

I like to keep my calendar data in separate table but you can add month and year fields to the main table if you want

QlikSense expression to find average duration over a period of time

1 Answers1