Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_ID | JOBCODE | WAREHOUSE | ASS_DET | START_DATE | START_TIME | END_DATE | END_TIME | SUSPEND_TIME | ASSIGNMENT TIME_WORKED | ASSIGNMENT_ID |
1 | IEDNA | 3 | 1 | 20/10/2013 | 22:08:10 | 20/10/2013 | 22:08:10 | 0:00:00 | 0:00:00 | 76044380 |
1 | IBDNA | 3 | 1 | 20/10/2013 | 11:56:39 | 20/10/2013 | 11:59:13 | 0:02:34 | 0:02:34 | 76043273 |
1 | IADTR | 3 | 1 | 20/10/2013 | 11:59:13 | 20/10/2013 | 22:08:10 | 0:32:26 | 10:08:57 | 76043286 |
1 | ILUNP | 3 | 2 | 20/10/2013 | 11:59:13 | 20/10/2013 | 22:08:10 | 0:32:26 | 10:08:57 | 76043286 |
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
Hello,
You can try this :
Sum({<[Jobcode] -= {'ILUNP'}>}) [Assignment Time Worked])
Regards,
Yoann
Try this
=Sum({<Jobcode={ILUNP}>}[Assignment Time Worked])
Try This
=Sum({<Jobcode={"*"}-{ILUNP}>}[Assignment Time Worked])
Thank You so much Raj, this does it perfectly
Amber
Welcome Amber..... still it is showing not answered question
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?
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])
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,