6 Replies Latest reply on Apr 10, 2018 6:26 PM by swaroop.gantela

    Help Calculating New Values Month over Month

    Felix Melendez

      swaroop.gantela,

       

      Previously you helped me enormously in calculating Persistence Values Month over Month.  From this discussion Thread: https://community.tableau.com/message/712764#712764
      Greatly appreciated!

       

      But I have not been able to address another calculation I want to accomplish. I am using the same data set but this time to address the New Values Month over Month.

       

      I have the current Alarm month over month that shows this:

       

      Persistence Calculation is the following:

       

      IF MAX([Current Alarm Count])=1

      AND PREVIOUS_VALUE(MAX([Current Alarm Count]))=1

      THEN 1 ELSE 0 END

       

      It shows the following:

       

      This is my approach to New Alarms month over month:

      IF MAX([Current Alarm Count])=1

      AND PREVIOUS_VALUE(MIN([Current Alarm Count]))=0

      THEN 1 ELSE 0 END

       

      I get the following:

       

       

      As you can see the month of Jan-17 shows 0 instead of 532.  I don't understand why it does that.  Am I calculating the new values month over month incorrectly?

       

      Any help will be appreciated!

       

      Thanks!

        • 1. Re: Help Calculating New Values Month over Month
          swaroop.gantela

          Felix,

           

          The Persistence calculations were from the very first month

          and then seeing if that carried forward, so those used PREVIOUS_VALUE.

           

          For this calculation of Month to Month, you can use LOOKUP

          to just compare the current month value to LOOKUP-1.

           

          To include the values for the very first month,

          there is a separate conditional in the beginning:

           

          IF [Index]=1 THEN
              IF MAX([Alarm Status])=1
              THEN 1 ELSE 0 END

          ELSEIF MAX([Alarm Status])=1
          AND LOOKUP(MAX([Alarm Status]),-1)=0
          THEN 1 ELSE 0 END

           

          Please see the last two sheets of the workbook attached

          in the Forum Thread.

          2 of 2 people found this helpful
          • 2. Re: Help Calculating New Values Month over Month
            Felix Melendez

            swaroop.gantela,

             

            Great! this achieved what I was looking for. Thanks!

             

            If I want to apply the opposite, where I would Identify the Drop-out values.

             

            The code would look like this correct?

             

             

            IF [Index]=1 THEN

                IF MAX([Alarm Status])=1

                THEN 1 ELSE 0 END

             

            ELSEIF MIN([Alarm Status])=0

            AND LOOKUP(MAX([Alarm Status]),-1)= 1

            THEN 1 ELSE 0 END


            Thanks in Advance!! You Rock!

            1 of 1 people found this helpful
            • 4. Re: Help Calculating New Values Month over Month
              swaroop.gantela

              Felix,

               

              Apologies, I've been out.

               

              Your code for the drop-out values looks reasonable to me.

               

              I don't think that the MIN/MAX parts are critical to the calculated field.

              Since I think it is looking at one row at a time for this calculation,

              the MAX will be the same as the MIN.

              It's just that LOOKUP needs some kind aggregation.

               

              Please see "Drop-Out Dash"  in the attached workbook.

              1 of 1 people found this helpful
              • 5. Re: Help Calculating New Values Month over Month
                Felix Melendez

                swaroop.gantela,

                 

                You're correct! I noticed the MAX/MIN did not make a difference. So I had change it and looking now at your workbook. I approached it the same way as you.

                 

                I've learned a lot with your guidance here. Getting more comfortable with making these calculations.

                 

                Thanks!

                 

                I have been playing with my data set and I noticed that with my consistent numbers I have persistent as well. Meaning that if I select a month window I may have drop out  in a month but then it comes up the next month.

                 

                For example:

                 

                Let's say I select 5 month and within 5 month I had an alarm 3 out of 5 month. That would be a persistent as opposed 5 out of 5 month which is consistent.

                 

                I have tried approaching it in many ways but can't seem to find a way to capture them all. So depending on # of month I select I would like to see what is a persistent alarm also.

                 

                I tried to approach it like this:  3 out of 5 example

                 

                IF [Index]=1 THEN

                    IF MAX([Alarm Count])=1

                    THEN 1 ELSE 0 END

                 

                 

                ELSEIF MAX([Alarm Count])=1

                AND LOOKUP(MAX([Alarm Count]),-1)=1

                AND LOOKUP(MAX([Alarm Count]),-2)=1

                AND LOOKUP(MAX([Alarm Count]),-3)=1

                AND LOOKUP(MAX([Alarm Count]),-4)=1

                THEN 1 ELSE 0 END

                 

                But this is a static way of doing it.  How can I modify this to align with the amount of month I have on my data set?

                 

                Currently I have a static month calculation field that goes like this.

                 

                 

                Month to Value#

                 

                IF [MONTH]= "03/01/2017" THEN 1

                ELSEIF [MONTH]= "02/01/2017" THEN 2

                ELSEIF [MONTH]= "01/01/2017" THEN 3

                ELSEIF [MONTH]= "12/01/2016" THEN 4

                END

                 

                Select n-month window filter

                IF [Month to Value (#)]<=[n-Months Alarms ]  // n-Months Alarms is a paramater

                Then 1 else 0 END

                 

                Parameter is created like this:

                 

                When I select a n-month it provides me a windows of months to display.  But I am having a hard time to determine what is a persistent alarm.

                • 6. Re: Help Calculating New Values Month over Month
                  swaroop.gantela

                  Felix,

                   

                  I'm not sure if this quite addresses your requirements, but I would like to

                  present a general approach to avoid hardcoding months.

                   

                  It would be to WINDOW_SUM the flags only of particular months that are in the window that you want,

                  and then use that sum to define your conditions of Consistent or Persistent.

                   

                  In this example, I arbitrarily define Consistent as > 50% of the alarm window:

                   

                  IF WINDOW_SUM ( IF [Show Alarm Window] THEN [LookbackStatus] END ) / [Alarm Window] >= .5

                  THEN 1 ELSE 0 END

                   

                  (the 50% could be a parameter).

                   

                  Persistent Flag can be:

                  IF WINDOW_SUM(IF [Show Alarm Window] THEN [LookbackStatus] END ) = [Alarm Window]

                  THEN 1 ELSE 0 END

                   

                  Please see last three tabs of the attached.