2 Replies Latest reply on Sep 22, 2016 8:04 AM by Joe Oppelt

    Matched Cohort - with "if" statement (can't use with boolean)

    . Berrett.Rice

      I have the following calculation (also see attached) which is giving me a Matched Cohort of students (a group of students who were present in all of the previous three years).

       

      {FIXED [State ID]: MAX(IIF([Year]=[Current Year] AND [Grade] = [Current Grade],1,0))}=1

      AND

      {FIXED [State ID]: MAX(IIF([Year]=[Current Year]-1 AND [Grade] = [Current Grade]-1,1,0))}=1

      AND

      {FIXED [State ID]: MAX(IIF([Year]=[Current Year]-2 AND [Grade] = [Current Grade]-2,1,0))}=1

       

      Current Year and Current Grade are parameters. The formula works but I have the following problem: When I'm looking at a 3rd grade student there are not two previous years worth of data (we don't start testing until 2nd grade). So the display shows up blank when Current Grade is set to 3.

       

      I'd like to build in some logic that says, If the user chooses Grade 3 then only show one previous year. If they select Grade 2 then only show the current year with no previous years. If they select any other grade then go ahead and show all three years.

       

      However, when I try to build in an If statement I get the error that If statements can't be used with Boolean data.

       

      Is there a way to get around this? Or a simpler approach to the problem that I'm not thinking of?

      (All of the data are protected so I hope we can work this out without an attached packaged workbook - if not, I can try to anonymize it somehow. Thanks.)

        • 1. Re: Matched Cohort - with "if" statement (can't use with boolean)
          Andrew Watson

          Does this work? It hasn't been tested so the syntax maybe off. Effectively it's summing your FIXED fields to check for either 1, 2 or 3, grade dependent.

           

          It returns True or NULL - effectively a boolean but not seen by tableau as boolean, seen as text.

           

          IF [Current Grade] = 2 AND SUM({FIXED [State ID]: MAX(IIF([Year]=[Current Year] AND [Grade] = [Current Grade],1,0))}) = 1 THEN 'True'

          ELSEIF [Current Grade] = 3 AND SUM({FIXED [State ID]: MAX(IIF([Year]=[Current Year] AND [Grade] = [Current Grade],1,0))} + {FIXED [State ID]: MAX(IIF([Year]=[Current Year]-1 AND [Grade] = [Current Grade]-1,1,0))}) = 2 THEN 'True'

          ELSE SUM({FIXED [State ID]: MAX(IIF([Year]=[Current Year] AND [Grade] = [Current Grade],1,0))} + {FIXED [State ID]: MAX(IIF([Year]=[Current Year]-1 AND [Grade] = [Current Grade]-1,1,0))} + {FIXED [State ID]: MAX(IIF([Year]=[Current Year]-2 AND [Grade] = [Current Grade]-2,1,0))}) = 3 THEN 'True'

          END

          1 of 1 people found this helpful
          • 2. Re: Matched Cohort - with "if" statement (can't use with boolean)
            Joe Oppelt

            THis has been solved in this thread:

             

            Make this LoD calculation NOT boolean

             

            In essence, the solution there is the same as you offered here, Andrew.

             

            I'm marking your answer as correct to close out this thread.

            1 of 1 people found this helpful