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

# Help Calculating New Values Month over Month

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

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

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
• ###### 3. Re: Help Calculating New Values Month over Month

Any thoughts?  Thanks

• ###### 4. Re: Help Calculating New Values Month over Month

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

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

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.