How to solve problem with multiple rows disturbing sum of a field?

[#1]

Hi

I have a table with information about participants in a program. Each participant occupies several rows based on what parts of the program they already have implemented.

When I try to sum and avg number of days they are active in the program it gets wrong becasue the calculation goes on evry row and not by studentID. The calculation are done by Sum(Days) But then I get the sum of all rows. How can I fix this?

The field Days are calculated in the script by Date(Stop) - Date(start) +1 as Days.

EDIT: Attached qvd and qvw

[#2]

Sorry for the delay in my answer...

My bad, I mixed up the student´s. But look at student 1517 and 2547. They both have 8 days in training. And the calculation are correct by each of them but I would also want to now how many days they have together. Two students with 8 days each should be 16 days together. 

It works fine when the students don´t have exactly the same number of training Days (and it´s not many times that occurs but it would be nice if it works)

[#3]

A other idea that stroke me, if it´s possible... Maybe only flag the highest number of 'prev education' and only Count the rows with that flag. Should´t that give only one row for each student?

[SergeyMak]

Did you try

Avg(Aggr(sum(Days), StudentID))

Regards,

Sergey

[#5]

Yes. But the calculation goes wrong when the student have multiple rows. ie student 1517 that have 7 rows in the datamodel beacuse the student have 7 registred prev working experiences. Avg(Aggr(sum(Days), StudentID) then gives 56 and not the supposed 8.

[maxgro · Accepted solution]

starting from Alessandro's answer, first expression in image

try with (second expression)

sum(aggr(Sum(DISTINCT Aggr(NODISTINCT Days,StudentID, StartDat)),StudentID,StartDat))

[#7]

Really nice! thanks. So that´s make a new sum with one more aggr out of the first one. cool