3 Replies Latest reply on Oct 26, 2018 8:07 AM by Tony R

    Multiple if Statement Help

    Tony R

      Hi,

       

      Need some help, as i'm brand new to Tableau. I know it's best to be able to post a workbook, but due to company security i'm not able to do so.  I have a statement that I started using that almost works, but is missing a key part.

       

      This current calculation works for everything, but not for "Green Flat", as "Green flat" should appear when the last 3 months are blank or 0.

       

      Current if calculation:

       

       

      IF [Difference +/-] >= 1 THEN " Red Up"
      ELSEIF [Difference +/-] = 0 THEN "Green Flat"
      ELSEIF [Difference +/-] > -1 AND [Difference +/-] < 1 THEN "Yellow No Change"
      ELSEIF [Difference +/-] <= 1 THEN "Green Down"

      END

       

       

       

       

      Difference +/- calculation:

      IFNULL( SUM([Previous month]) - sum([Previous 3 Month Average]),0)

       

      Previous month calculation:

      ifnull

      (IF

      DATEDIFF('month', DATETRUNC('month', [Date]),

      {MAX(DATETRUNC('month', [Date]))})=1

      THEN [Number of Records]

      END, 0)

       

      Previous 3 month calculation:

      ifnull( if datediff('month',[Date],today())<=3 and datediff('month',[Date],today())>=1 then [Number of Records]/3 end, 0)

       

      Below example:

       

      Orange bar row should show: Green Flat

       

      Navy bar row should show: Yellow Flat, as there is data in the bar. Last 3 month average is 1 (0+2+1) / 3 = 1 and Difference +/- = 0 so it shows Green flat based on current formula, but it should be yellow flat. I can accomplish this by removing green flat from calculation, but then orange bar doesn't show Green flat.

       

      Light blue should show yellow flat

        • 1. Re: Multiple if Statement Help
          swaroop.gantela

          Tony,

           

          I did not fully catch the gist, so my apologies if I'm way off base.

           

          If you only want to show green flat when the previous three months

          are blank or 0, then I think you will need to use a separate conditional other

          than comparing zero to Difference +/- .

           

          Not exactly sure how it would work, but one example would be,

          if the Number of Records for a month will never be negative,

          then you could write a separate conditional without the divide by 3:

           

          ifnull( if datediff('month',[Date],today())<=3 and datediff('month',[Date],today())>=1 then [Number of Records] end, 0)

          and then check if that sum is equal to zero.

           

          So the overall IF/THEN would be

          IF [Difference +/-] >= 1 THEN " Red Up"
          ELSEIF SUM([AllBlankorZero]) = 0 THEN "Green Flat"
          ELSEIF [Difference +/-] > -1 AND [Difference +/-] < 1 THEN "Yellow No Change"
          ELSEIF [Difference +/-] <= -1 THEN "Green Down"

          END

           

          Also, should Green Down be for when Difference +/- is less than negative 1?

           

           

           

          1 of 1 people found this helpful
          • 2. Re: Multiple if Statement Help
            Mahfooj Khan

            Hi,

             

            I guess your IF conditions malfunctioning and quite confusing

             

            IF [Difference +/-] >= 1 THEN " Red Up" //If this condition met then your Green Down will never appear because you've put here>=1, It means If difference is greater or equals to 1 then It will be red but again in case of Green Down you've put <=1 which is again confusing and it will be skipped from that condition.

            ELSEIF [Difference +/-] = 0 THEN "Green Flat" //Once it meet the condition then it will not be part of the next ELSEIF condition

            ELSEIF [Difference +/-] > -1 AND [Difference +/-] < 1 THEN "Yellow No Change" // When you're putting condition >-1 and <1, zero is also falling in that range

            ELSEIF [Difference +/-] <= 1 THEN "Green Down" //Only negative values <-1 will be in Green Down

            END

             

            You can try this not sure whether it will work or not

            IF 

            [Difference +/-] < 0 THEN "Yellow No Change"

            ELSEIF [Difference +/-] = 0 THEN "Green Flat"

            ELSEIF [Difference +/-] <= 1 THEN "Green Down"

            ELSEIF [Difference +/-] > 1 THEN " Red Up"

            END

             

            You can create a sample mock up data which matches with structure of your original source in a packaged workbook and share with us.

             

            Mahfooj

            • 3. Re: Multiple if Statement Help
              Tony R

              Awesome, this really helped me out. Thanks for your assistance.