Alerter Based on Previous Results in Column

system · November 3, 2011, 7:14pm

I am in need of an Alerter which is based on previous results in the column. Can’t figure out a way to do this. Any suggestions?

Specifically, I have a column of blood glucose results (numbers). I need an alerter that will highlight each case where there are three consecutive values equal to or greater than 250.

I am also encountering an issue with the simple alerter I started with. I set up the initial alerter to highlight each value in this column that is equal to or greater than 250, but in random cases it is highlighting numbers less than 250. The formula is simple (=[Result Value]>=“250”) and I don’t see how it would be making that error occur.

Thanks in advance for the help.

williamdholmes (BOB member since 2011-09-29)

system · November 3, 2011, 7:22pm

Moderator note:
Please let us know which Bo tools - WebI, DeskI, Crystal - you use so the topic can be moved to a proper forum. Thank you.

And welcome to B:bob:B!

Marek Chladny (BOB member since 2003-11-27)

system · November 3, 2011, 7:50pm

Based on the formula, I’m going to assume Web Intelligence for now. :?

Create a variable called flag, with the following formula:

=If([Result Value]>=250 And Previous([Result Value])>=250 And Previous(Previous([Result Value])) >=250;1;0)

Then create a Alerter that highlights the rows where the flag variable is equal to 1. It will highlight the 3rd consecutive value that is greater than or equal to 250. Here’s my test:

MichaelWelter (BOB member since 2002-08-08)

system · November 3, 2011, 8:26pm

Yep, BOXI WebI. Sorry I left that out.

Thanks for the tip! I just learned more here on the BoB than I did in the two day BOXI seminar we were provided.

williamdholmes (BOB member since 2011-09-29)

system · November 3, 2011, 8:29pm

[Moderator Note: Moved from General Discussion to WebIntelligence XI]

Marek Chladny (BOB member since 2003-11-27)

system · November 7, 2011, 6:23pm

The variable and alerter work, but occassionally there are multiple random highlights where the value is less than 250.

Any idea what could be causing this?

williamdholmes (BOB member since 2011-09-29)

system · November 7, 2011, 10:28pm

What is your formula for the flag variable?

MichaelWelter (BOB member since 2002-08-08)

system · November 8, 2011, 1:26pm

The flag variable formula is

=If([Result Value]>=250 And Previous([Result Value])>=250 And Previous(Previous([Result Value])) >=250;1;0),

as you suggested.

williamdholmes (BOB member since 2011-09-29)

system · November 8, 2011, 2:47pm

Add the flag variable to your table and see if it is putting a 1 on a row where it shouldn’t. I don’t see any way that it can.

MichaelWelter (BOB member since 2002-08-08)

system · November 9, 2011, 3:24pm

Somehow it is putting a 1 where it shouldn’t. Take a look.

Image Link - Improper Flag

williamdholmes (BOB member since 2011-09-29)

system · November 9, 2011, 3:44pm

I have no idea why that’s happening. Can you save the document for all users, and send it to me, or attach it here?

MichaelWelter (BOB member since 2002-08-08)

system · November 9, 2011, 3:51pm

Have you tried the Relativevalue() function, instead?

Mak 1 (BOB member since 2005-01-06)

system · November 9, 2011, 4:07pm

I’ve also ran into this issue when applying a simple alerter which would highlight each instance where the result is >=250. I just don’t get it…

Michael - unfortunately I cannot make the document available due to HIPPA law.

Mak1 - I’m not sure how you would use Relativevalue() function in this case. Can you expound on that idea?

williamdholmes (BOB member since 2011-09-29)

system · November 10, 2011, 9:12am

I posted an example here:-

Have you got a “slicing” dimension, in the block?

Mak 1 (BOB member since 2005-01-06)

system · November 10, 2011, 4:46pm

Thanks for the example, but I still don’t see how it’s applicable here.

I solved the issue with a little help from a friend - turns out the result was being returned as a string. By creating an object using the =ToNumber() function, I was able to apply the alerter to the new data object with no problems.

Thanks to all for the help!

williamdholmes (BOB member since 2011-09-29)

system · November 10, 2011, 5:10pm

Glad you solved it :), that makes sense now.
Relative Value works pretty much the same as previous apart from you can step forward and backwards a number of rows, you do this with a postive or negative number. You can also use it with No filter().

Mak 1 (BOB member since 2005-01-06)

system · November 10, 2011, 7:33pm

Thanks for the clarification on Relative Value, makes sense now.

williamdholmes (BOB member since 2011-09-29)