Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor 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
Honored Contributor III

Re: sum problem

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

Highlighted
Honored Contributor III

Re: sum problem

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;

Highlighted
Contributor III

Re: sum problem

NO

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

Highlighted
Honored Contributor III

Re: sum problem

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;

Highlighted
Contributor III

Re: sum problem

I DONT WANT TO CREAT ANOTHER TABLE,

I WAND AN EXPRESSION THAT CAN MAKE IT.

SOMETHING LIKE:

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

Highlighted
Not applicable

Re: sum problem

can you post a sample app?

and capslock broken?

Highlighted
Not applicable

Re: sum problem

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

Can you please share QVW.

Highlighted
Not applicable

Re: sum problem

Try this expression,

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

Highlighted
Contributor III

Re: sum problem

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)