Hello,
If have a list of values being collected over time [value column] [date column]
How can i trigger a warning to show Data is trending down over time
or, the current years values is less than last years value by 250
List of values could could be
350,1000,400 ( Warning)
or 350,1000,1500 ( no warning)
or 1200,900,500 ( Warning) etc etc - this data trend is the ideal situation
Thank you for your help
This is interesting and it's doable, can you share a real usecase?
Thinking out loud:
Yes .
example :
In 2019 a test was conducted on a water line to measure gallons per minute. The test result was 350 gpm.
each year the test is repeated and a new gpm value collected .
Overtime the values for most stay pretty consistent however, there are times a value may spike . These usually have an explanation like a new line was installed or perhaps the tester didn’t perform the test correctly.
Other times the value may drop significantly or if analyzed , may show it has been dropping over time . This could also be a problem with the tester or it could indicate there is an issue with the line that needs looked into and maybe the line needs replaced .
I found a way to do this. Not exactly what i wanted to do because i would rather avoid using virtual tables.
Anyway , I set a virtual column [Analyze]to grabs the values in a list (all the test done for that ID)from another table.
I set another column to count how many values where in that [Analyze] column
After that i pulled the values from the different positions using index . Column for [Most recent Year], [Last Year] and [Two Years Ago]
I knew the count could vary so i set it the count to 3 to represent 3 years of data.
The final expression was successful in pulling data that has trending down over the last 3 years , or just 3 years worth of testing (most recent could be last year and worked backwards from there)
If(And([Flow tests] = 3,[Two years Ago] >[Last year],[Last year] > [Most Recent Year]), True, False)
That took forever
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |