How to create "except" in qliksense?

[jim_chan]

Hi Guys,

this could be a tricky question, i find it super tough .  Ok, scenario, i need to calculate mandays hours, I have 2 columns as below, i have 2 text object - obj1( is for Functional) obj2(BW), both uses only 1 column(Hours_Functional).

Therefore when user select Project_ID - AA-ABCD, the Function Object shows 0, BW Object shows 120, this is what i wanted to be.

BUT, right now , my result is, when user select Project_ID - AA-ABCD, Function Object shows 120, BW Object shows 120, is wrong for Function Object,should be 0.

Expression that i have given to BW Object is sum({<[Hours_Functional], [Project ID] = {'AA-ABCD'}>} Total [Hours_Functional])

and Function Object is SUM({<[AA-ABCD]=>} [Hours_Functional])

Project_ID	Hours_Functional
AA-ABCD	120
AA-EFGH	0

[Anonymous]

Hello ,

If you use TOTAL clause the function works like without a dimension. So the result is normal.

What is your purpose ? I did not get it.

You can send an app.

BR

Serhan

[Miguel_Angel_Baeyens]

Hi,

I'm not really getting the point of the calculation, but I do can see some syntax errors in your set analysis and expressions.

First, when using TOTAL you mean all data, therefore the BW Object expression should return the value in Hours_Functional for all Project_ID = AA-ABCD, regardless which value do you have selected.

Second, using [Hours_Functional] without any filter in the set analysis part should return an error (either a null or the full aggregation). Same with Function objet and [AA-ABCD] that should be instead (my guess):

Sum({< Project_ID = {'AA-ABCD'} >} Hours_Functional)

Basically, set analysis modifiers always have two parts and they are always the same:

{< FIELDNAME OPERATOR VALUE >}

• Fieldname is the field you want to filter before the chart or object is rendered. It must be a field name case sensitive as existing in the data model. I cannot be an expression or variable.
• Operator can be =, +=, -=, *=, check the Ref. Manual for more detailed info.
• Value can be any expression valid in UI for example {'AA-ABCD'} for a single literal value or {"=Sum(Field) > 2000"} for a more complex expression.

More on set analysis here:

A Beginners' Introduction to Set Analysis (video by Mike Tarallo)

http://community.qlik.com/message/130723#130723 (some examples and syntax)

Miguel