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

[bbi_mba_76]

Hi,

I think you have to use the aggr function.

Could you provide an example with some data?

[alexandros17]

This request can be achieved in several ways, the problem is related to the data model.

Post your document and I'll try to help you

[sudeepkm]

it looks like you are looking for an aggregation of working days by participants.

you can create a pivot table keeping aggr(sum(Days),StudentID) which is equivalent to the sum of days group by Student ID

[#5]

Attached qvd to original post

[alexandros17]

I sent you an example (I addede a sheet)

Let me know

[#7]

Thanks. A good start but the sum don´t work when you have 2 student with the same number of days. ie student 211 and 1246

[alexandros17]

So I have not understood the logic, I tried to group By StudentID and StartDat,

for the 211 we have 5 Days (Started at 2014-04-25) and 1694 Days Started at  2009-08-18

for the 1246 we have only 5 days starting 2011-01-27

If so sums are correct, otherwise tell me the correct logic ...

[#9]

Yes it's correct by student but total sum should be 10(2*5)

[alexandros17]

I don't undertand why you exclude 1694 days?