Dynamic Chart

Wouldn't it be great to update your charts instantly after you received a new response?

With the help of the Google Sheets Query function, this becomes super easy.

The Example:

Among other meter values you're collecting information of your water meter for a while now. All response values are stored at your Google Form response Sheet. In order to filter out only your water meter values, use following formula at a new sheet: =QUERY('MaintainApp Responses'!A1:H, "SELECT A, B where B > 0 order by A desc",1)) -> this will filter all your water meter values at column B, and sort it decending of your Timestamp (column A).

now, if a new value comes in, the chart will automatically be updated:

Dynamic Limit

as you can see at the example above, you can calculate values with your filtered data. This information you can use as a [Lower Limit Value] or [Upper Limit Value] at your <settings> sheet.

By doing that you will be notified only if a response value is outlied.

Calculate the m³/day with following formula: =IF(A3=0,"", IFERROR((B3-B2)/(A3-A2)))

in order to check whether the inserted value is smaller than the last inserted value (which makes for a water meter no sense), just make cell B3 to your lower limit. If don't want to be notified in case the meter value remains constant, just subtract 0.0001 from cell B3 -> ='Water Meter Evaluation'!B3-0.0001

Background: The response value of your form is FIRST WRITTEN to the sheet AND THEN CHECKED by MaintainApp. if you choose cell B2 the responded value will always be the same as your Min value and therefore never send an E-Mail.

Dynamic meter limit

It is also easy to check your (constantly increasing / decreasing) responsed meter value against an expected meter value at response time.

To check whether the water meter value is within the X-time standard deviaton of Y-time measurements, calculate the value as followed: =$B$3+($A$2-$A$3)*$L$2 and make it as your [Upper Alert Threshold]

L2 equals =AVERAGE($K$2:$K$15)+1*STDEV($K$2:$K$15) --> here: X = 1; Y = 14

And that's it. No fix limits, more dynamic control of your processes!

To sum up: It is very easy to make your limit and threshold dynamic. You will get notifications if your process is changing more than expected, without doing any manual calculation whatsoever.

Dynamic Reminder Date

as you can see at the examples above, you can calculate values with your filtered data. This information you can use as well to adjust the PreReminder, Reminder and Escalation Reminder and therfore the frequency this question needs to be checked / is escalated.

An easy example would be, if the last calculated {water meter value per day} exceeded the {average + standard} deviation limit, the [Reminder Interval] will be 2 days, otherwise 5 days:

Formular [Reminder Interval] equals =if('Water Meter Evaluation'!K2>'Water Meter Evaluation'!L2,2,5)

Dynamic Notification

Following the same principles as show above, you can also generate [Notification]s if several response values meet a certrain criteria.

If your criteria is met, just write "AnyValue" at the [Notification] cell.

Just keep in mind: The first value at the top of the querey-command is the last submitted response (and not the last value)

Page updated

Google Sites

Report abuse