Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.