2 Replies Latest reply on Oct 12, 2013 7:09 AM by Minh Nguyen

    Combine logical functions in calculated fields?

    Minh Nguyen

      Dear all,

       

      I tried to do combine some calculated fields together but somehow I can't.

       

      I have some calculated fields

      1/  "Selected based on PCA cutoff user 1" which gives me 0 and 1. 

      iif([Include_threshold PCA]=1,iif([totalscore]<=[PCA Cutoff score ],1,0),1)*[Combined options]

       

      2/ "Selected based on PCA cutoff user 2"  which gives me 0 and 1.    

      iif([Include_threshold PCA]=1,iif([totalscore]<=[PCA Cutoff score ],1,0),1)*iif([Include_dependency ratio]=1,[High depratio],1)

       

      They work separately fine as expected. However, I would like to combine them together, like union not interaction, but it seems when I tried those, none worked.

       

      option 1)

      if [Selected based on PCA cutoff user 2]==1 OR [Selected based on PCA cutoff user 1]==1 then 1

      else 0

      end

       

      option 2) I tried to sum the first and second, then if the sum>=1 then 1 else 0, but it did not work either.

       

      option 3)

      iif([Include_threshold PCA]=1,iif([totalscore]<=[PCA Cutoff score ],1,0),1)

      *iif(iif([Include_dependency ratio]=1,[High depratio],0)=1 OR [Combined options]=1,1,0)

       

      Is there any function (union) that I can use to do so?

       

      I attached the sample workbook so that anyone can have a look (mainly sheet 15 but sheet 16 and 17 will help for the 2 separate fields).

       

      Any help is much appreciated.

       

      Minh

        • 1. Re: Combine logical functions in calculated fields?
          Shawn Wallwork

          Minh, I think all you need to do is drag all those calcs you created from the Dimension pane to the Measures pane. Then when you drag them into the view they all seem to work:

           

          Measures.png

           

          I didn't change any of your calcs, I just made them measures. Is this what 'working' looks like?

           

          --Shawn

          • 2. Re: Combine logical functions in calculated fields?
            Minh Nguyen

            Many thanks Shawn.

             

            Not that one, even when you put them in measures, the values inside are not correct. I am really want to put them as Dimensions.

             

            But I think I figure out another way to combine that with another cals.

            if [Select parameters]==1 and [Include_dependency ratio]=1 then

                [Selected based on PCA cutoff user 2]

            elseif [Select parameters]!=1 and [Include_dependency ratio]=1 then

                [Selected based on PCA cutoff user 0]

            elseif [Select parameters]!=1 and [Include_dependency ratio]=0 then

                [Selected based on PCA cutoff user 1]

            elseif [Select parameters]==1 and [Include_dependency ratio]=0 then

                iif([Include_threshold PCA]=1,iif([totalscore]<=[PCA Cutoff score ],1,0),1)

            end