sum problem

[adiarnon]

hi'

i have this table -

cust	date	hours
A	1.1.14	9
B	2.1.14	9
C	3.1.14	9
A	1.1.14	9
C	2.1.14	9

I WANT TO GET THIS -

CUST	HOURS
A	9
B	9
C	18

I WANT IT TO SUM FOR EACH CUST THE HOURS BUT ONLY FOR DISTINCT DATES.

LIKE- 'A' have two records with the date -1.1.14 so it need to count it only once...

hope its clear

TNX

[#2]

Check this ...

[anbu1984]

LIke this

[er_mohit]

see the attached file

[adiarnon]

i cant open the files =(

can someone explain in word the solution?

[adiarnon]

not working )=

[jerem1234]

Hello, I used the formula:

sum(aggr(firstsortedvalue(distinct hours, -hours), cust, date))

This will get the highest hours for a date (if they happen to be different for instance if you had A 1.1.14 to have 9 and 11 for hours), then sum all the hours for the different dates for a customer. I believe this is what you are looking for. Then work hours is:

sum(work_hours)

Please find attached.

[anbu1984]

Script:

-------------------------------------------------------------

Tab1:

Load * Inline [

cust,date,avail_hours,work

A,1.1.14,9,2

B,2.1.14,9,3

C,3.1.14,9,4

A,1.1.14,9,5

C,2.1.14,9,6 ];

Final:

Load *,If(RowNo() <> 1 And cust = Previous(cust) And date = Previous(date),1,0) As Ind Resident Tab1 order by cust,date;

Drop table Tab1;

-------------------------------------------------------------

Use these expressions in front end

sum(If(Ind=0,avail_hours))

sum(work)