
1. Re: Help Calculating New Values Month over Month
swaroop.gantela Apr 2, 2018 6:52 PM (in response to Felix Melendez)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 LOOKUP1.
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 ENDELSEIF MAX([Alarm Status])=1
AND LOOKUP(MAX([Alarm Status]),1)=0
THEN 1 ELSE 0 ENDPlease see the last two sheets of the workbook attached
in the Forum Thread.

257091persE_new_v103.twbx 64.4 KB


2. Re: Help Calculating New Values Month over Month
Felix Melendez Apr 2, 2018 11:44 PM (in response to swaroop.gantela)1 of 1 people found this helpfulGreat! this achieved what I was looking for. Thanks!
If I want to apply the opposite, where I would Identify the Dropout 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! 
3. Re: Help Calculating New Values Month over Month
Felix Melendez Apr 6, 2018 10:35 AM (in response to Felix Melendez) 
4. Re: Help Calculating New Values Month over Month
swaroop.gantela Apr 9, 2018 3:31 PM (in response to Felix Melendez)Felix,
Apologies, I've been out.
Your code for the dropout 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 "DropOut Dash" in the attached workbook.

257091persF_v103.twbx 72.2 KB


5. Re: Help Calculating New Values Month over Month
Felix Melendez Apr 10, 2018 6:54 AM (in response to 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 nmonth window filter
IF [Month to Value (#)]<=[nMonths Alarms ] // nMonths Alarms is a paramater
Then 1 else 0 END
Parameter is created like this:
When I select a nmonth 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 Apr 10, 2018 6:26 PM (in response to Felix Melendez)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.

257091persG_v103.twbx 88.9 KB
