5 Replies Latest reply on Dec 30, 2016 11:21 AM by Sarah McGraw

    Help with formula in set (error: "Cannot use 'boolean' type in 'IF' expression")

    Sarah McGraw

      Hi,

       

      I'm learning to use sets for a new project.

       

      I have made a number of customer sets using the "by field" option on a date field to capture customers with the type of activity captured on that date ([dateofactivity] COUNT > 0). Now I need to introduce a date restriction into the set as well, and I'd like the date restriction to be chosen using a parameter control.

       

      This is what I have in mind:

       

      IF DATETRUNC('quarter',[dateofactivity])=[parameter control]

      THEN COUNT([dateofactivity])>0

      END

       

      This formula gives me the error: "Cannot use 'boolean' type in 'IF' expression".

       

      How can I resolve this?

       

      Thank you!

        • 1. Re: Help with formula in set (error: "Cannot use 'boolean' type in 'IF' expression")
          Wim Kegels

          Hi Sarah,

           

          COUNT([dateofactivity])>0 is a BOOLEAN statement, not something you can use as a result of an IF statement.  You might want to try COUNT([dateofactivity]>0) instead.

           

          Kind regards,

           

          Wim

          • 2. Re: Help with formula in set (error: "Cannot use 'boolean' type in 'IF' expression")
            Sarah McGraw

            Hi Wim, and thank you for your answer.

             

            I tried adjusting the formula as you suggested, and now I get a "Cannot mix aggregate and non-aggregate comparisons or results in "If statements" error instead.

             

            I don't know whether this will make any difference or be helpful (and maybe it was already clear based on my first post), but this is the way the data is laid out:

             

            subjectID     ActivityDate1          ActivityDate2     ActivityDate3

                   1             8/1/2016                   NULL               8/1/2016

                   2             8/1/2016                 8/1/2016              NULL

                   3               NULL                    8/1/2016            8/1/2016

                   4             8/1/2016                 8/1/2016            8/1/2016

                   5             1/1/2016                 1/1/2016            1/1/2016  

             

             

            Therefore, for each activity, it is possible to tell whether the subjectID was involved during a particular quarter by the presence or absence of a date during that quarter. Each set should be the list of SubjectIDs where a particular activity date field is populated for any date during the chosen quarter.

             

            So for Parameter Control = 2016 Q3:

                 Set-Activity Date 1: {1,2,4}

                 Set-Activity Date 2: {2,3,4}

                 Set-Activity Date 3: {1,3,4}

             

            For Parameter Control = 2016 Q1:

                 Set-Activity Date 1: {5}

                 Set-Activity Date 2: {5}

                 Set-Activity Date 3: {5}

             

            Thank you for your help!

             

            Sarah

            • 3. Re: Help with formula in set (error: "Cannot use 'boolean' type in 'IF' expression")
              Bill Lyons

              A packaged workbook illustrating what you have so far would be most helpful. Please see Packaged workbooks: when, why, how.

              • 4. Re: Help with formula in set (error: "Cannot use 'boolean' type in 'IF' expression")
                Bora Beran

                Without an example workbook I can't tell if this will actually solve your problem but you can work around the error by rewriting the calculation this way

                 

                SUM(IIF(DATETRUNC('quarter',[dateofactivity])=[parameter control], 1, 0)) >0

                 

                this will count each row that matches the criteria of datetrunc matching parameter control as 1 and everything else as 0 then sum it up which will give you the count of items that satisfy the criteria. Then it will check if it is greater than 0.

                • 5. Re: Help with formula in set (error: "Cannot use 'boolean' type in 'IF' expression")
                  Sarah McGraw

                  I appreciate your reply, but I think the logic you outline won't work for my project, because I need my return set to be a list of SubjectIDs (because I will need to use them to create combined sets later). If I read your reply correctly, it will give me a count instead.

                   

                  I think I figured it out, though. I was foolishly trying to add the DATETRUNC() part into my existing set, which was filtering SubjectIDs by the presence of a date in another field. But obviously, in order for the DATETRUNC-parameter match to be true, there would have to be a date present in the first place... so half the formula was redundant (and now I feel sheepish).

                   

                  This worked for me in the formula field: DATETRUNC('quarter',MAX([datefield]))= [parameter]

                   

                  Easy peasy.

                   

                  Thank you!