2 Replies Latest reply: Mar 6, 2015 7:00 AM by Miguel Angel Baeyens de Arce RSS

    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_IDHours_Functional
      AA-ABCD120
      AA-EFGH

      0

        • Re: How to create "except" in qliksense?
          Serhan Celebi

          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

          • Re: How to create "except" in qliksense?
            Miguel Angel Baeyens de Arce

            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