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: 
Not applicable

Expression: Sum column if row is unique

I have three columns for arguments sake I will call them A, B, and C these fields are pulled in from a CSV file. The columns B and C need to be shown in a chart showing the following expressions

1) Sum(C)

2) The second Expression is what I am stuck on.

I need to show a sum of B but only if the row has a distinct combination of A, B and C.

Any help on where to start would be appreciated.

A: Is a workID

B: Is the total hours allocated to that workID

C: contains how many hours an employee worked on that workID

1 Solution

Accepted Solutions
Not applicable
Author

Hi

Is this what you want?

Mark sum of rows in expression tab.

Regards

Anders

View solution in original post

6 Replies
john_duffy
Partner - Creator III
Partner - Creator III

Hello.

Could you attach a sample application using inline load or give an example of the input data (actual values) and required chart.

Thanks.

Anonymous
Not applicable
Author

The first that comes to mind:
sum(if(count({1} A&B&C)=1, B, 0))
I guess it's better to define "uniqueness" in the script rather than in the expression, by counting the number of ABC combinations, and marking as unique if count=1

Not applicable
Author

Hi,

Thanks for the reply, here is some sample data

WOL.OpNoWOL.StdHoursWOL.HoursWOL.W/Order
101001.25WS61064
101003.75WS61064
101008.75WS61064
152000.25WS61064
152007WS61064
152007.25WS61064
152007.5WS61064
152008WS61064
152008.5WS61064
152008.75WS61064
152009.25WS61064
152009.5WS61064
152009.75WS61064
201000.25WS61064
201000.5WS61064
201001WS61064
201001.25WS61064
201001.5WS61064


WOL.W/Order Is the unique Job

WOL.OpNo Is an activity that makes up that job





WOL.StdHours Is the hours assigned to the OpNo



WOL.Hours Is a running total of hours worked on a OpNo

The chart needs to show a sum of WOL.Hours which is the easy part the next is what I am stuck on.

The WOL.StdHours for OpNo 10 is 100 if I simply sum it I get 300 when 100 is the correct figure in this example data I would need the graph to show 400 Hours alloted and 94 Hours taken.

The OpNo is in no way unique a differnt WOL.W/Order may contain the same OpNo.

Hope this clears things up and help you help me.

Regards

Steven

Not applicable
Author

Hi

Is this what you want?

Regards

Anders

Not applicable
Author

WOL.OpNoWOL.StdHoursWOL.HoursWOL.W/Order
101001.25WS61064
101003.75WS61064
101008.75WS61064
152000.25WS61064
152007WS61064
152006WS61064
152009.75WS61064
201000.25WS61064
20100.25WS61064


Thanks for your help but unfortunately I don't think so WOL.StdHours may not always be unique and yet still needs to be counted in this example both WOL.OpNo 15 & 20 have 100 hours so 100 would need to be counted twice. The graph in this example would show 400 hours.

Not applicable
Author

Hi

Is this what you want?

Mark sum of rows in expression tab.

Regards

Anders