Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adiarnon
Creator III
Creator III

sum problem

hi'

i have this table -

custdatehours
A1.1.149
B2.1.149
C3.1.149
A1.1.149
C2.1.149

I WANT TO GET THIS -

CUSTHOURS
A9
B9
C18

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

16 Replies
anbu1984
Master III
Master III

Do you have rows where cust and date are same and hours are different?

anbu1984
Master III
Master III

Tab1:

Load distinct * Inline [

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 ];

Final:

Load cust,sum(hours)Resident Tab1 group by cust;

adiarnon
Creator III
Creator III
Author

NO

BAT I HAVE MORE COLUMN IN THIS TABLE AND IS THOSE COLUMNS THE DATA ARE DIFFERENT

anbu1984
Master III
Master III

Tab1:

Load distinct cust,date,hours Inline [

cust,date,hours,col1,col2

A,1.1.14,9,1,2

B,2.1.14,9,11,22

C,3.1.14,9,22,33

A,1.1.14,9,44,55

C,2.1.14,9,66,77 ];

Final:

Load cust,sum(hours)Resident Tab1 group by cust;

adiarnon
Creator III
Creator III
Author

I DONT WANT TO CREAT ANOTHER TABLE,

I WAND AN EXPRESSION THAT CAN MAKE IT.

SOMETHING LIKE:

SUM({DISTINCT DATE} HOURS)   ?!?!?!?

Not applicable

can you post a sample app?

and capslock broken?

Not applicable

In Qlikview, it shows only distinct values in straight table.

Can you please share QVW.

Not applicable

Try this expression,

SUM({<DISTINCT DATE, CUST>} HOURS)

adiarnon
Creator III
Creator III
Author

i cant share a sample because i dont have QV in the computer with the internet.

i will try to do it more clear:

i have this table -`

custdateWorkAvailable hours
A1.1.1429
B2.1.1439
C3.1.1449
A1.1.1459
C2.1.1469

I WANT TO GET THIS -

CUSTAvailable HOURSWork Hours
A97 (2+5)
B93
C1810 (4+6)

i want in the available hours column to sum for each sust his available hours but not sum twise for the same date.

the work hours should just do - sum(work hours)