Duration Expression

[Toothsie]

Hi all, I want to write an expression in Qlik Sense that calculates the duration in hours of unique courses. For example, take the table:

                            Course         Duration       Attendee

                             Math              2:00                 Joe Bloggs

                             Math              2:00                  John Bloggs

                              History         1:00                   Jane Doe

I want an expression that calculates the total duration as 3 hours. ie. Math lasts 2 hours + History lasts 1 hour. So I only want to include unique courses. Any help is appreciated. 

[jerifortune]

You can do something like this 

 

hour_Calc:
Load * inline [
Course| Duration|Attendee
Math |2:00 | Joe Bloggs
Math |2:00 | John Bloggs
History |1:00 | Jane Doe

] (delimiter is '|');

tempHour:
Load
Course,
Duration,
if(Course=previous(Course),peek("RowPatiNO")+1,1) as "RowPatiNO"
Resident hour_Calc
ORDER BY Course
;

Drop table hour_Calc;

Hour:
Load
Course,
LEFT(Duration,LEN(Duration)-3) AS Duration
Resident tempHour
Where RowPatiNO = 1
;
Drop table tempHour;

Then, use sum function at the visualization e.g. sum(Duration)

[thi_pham]

I'm not sure what you have in your data, let's assume your provided table are your raw data, than I suggest this expression:

sum(aggr(avg(Duration), Course))

You can replace avg function by min or max and will get same result because I assume that all attendees got same duration of same course.

Hope it help.