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: 
amber2000
Creator
Creator

Exclude form Sum on base of Field value


Hello everyone,

I'm having trouble with an expression:

In a chart I have 2 Columns, Assignment Time Worked with expression Sum([Assignment Time Worked])

and Nbr Assignments with expression count (DISTINCT [Assignment ID])

Both Columns come from this table:

ASSOC_IDJOBCODEWAREHOUSEASS_DETSTART_DATESTART_TIMEEND_DATEEND_TIMESUSPEND_TIMEASSIGNMENT TIME_WORKEDASSIGNMENT_ID
1IEDNA3120/10/201322:08:1020/10/201322:08:100:00:000:00:0076044380
1IBDNA3120/10/201311:56:3920/10/201311:59:130:02:340:02:3476043273
1IADTR3120/10/201311:59:1320/10/201322:08:10  0:32:2610:08:5776043286
1ILUNP3220/10/201311:59:1320/10/201322:08:10  0:32:2610:08:5776043286
20:20:28

The problem is that there are 2 Jobcodes for Assignment 76043286 and the Time_Worked is added for all assignments.

I must exclude from Time_Worked when Jobcode value is ILUNP.

Tried to do it like this: =Sum(([Assignment Time Worked]) - (<[Jobcode] = {'ILUNP'}>))  but failes

These are my first steps in QlikView so any help is very welkom.

Thanks in advance,

Amber

1 Solution

Accepted Solutions
Not applicable

Try This


=Sum({<Jobcode={"*"}-{ILUNP}>}[Assignment Time Worked])

View solution in original post

8 Replies
yduval75
Partner - Creator III
Partner - Creator III

Hello,


You can try this :

Sum({<[Jobcode] -= {'ILUNP'}>}) [Assignment Time Worked])


Regards,


Yoann

Not applicable

Try this

=Sum({<Jobcode={ILUNP}>}[Assignment Time Worked])

Not applicable

Try This


=Sum({<Jobcode={"*"}-{ILUNP}>}[Assignment Time Worked])

amber2000
Creator
Creator
Author

Thank You so much Raj, this does it perfectly

Amber

Not applicable

Welcome Amber..... still it is showing not answered question

amber2000
Creator
Creator
Author

One more question, can you explain to me what the expression does?

Why don't we have to put quotes around the field ILUNP but around the wildcard there are double quotes?

yduval75
Partner - Creator III
Partner - Creator III

Hello Monique,


I think the expression below also operates with a shorter syntax,  the sum function will take all jobcodes without ILUMP.

You have to put '-=' before the value.


Sum({<[Jobcode] -= {'ILUNP'}>}) [Assignment Time Worked])

Not applicable

if there is any gaps between one field we have to put single quotes because it will take as a single field otherwise it shows error.... Gaps between same fields are not accepted in QV

in wild cards we have to put double quotes in set analysis but in if condition or wildmatch single quotes are acceptable,