Log In
Register
Home
February 15, 2022
Offline
Good day to whoever reads this.
I have a piece of equipment that outputs data for each test when it runs.
Each test is comprised of sub test headings 1 and 2, a measurement, a lower tolerance and an upper tolerance.
I have added the percentage inside tolerance as a calculation.
My problem is: How can I display a graph, only when the data is trending towards failure.
I have used slicers to manually achieve this, but the real bulk data is huge. I could have 1000s of lines of tests. I have only included 3 sub tests for each day in my example data to try and get my head around it.
Manual Slicer graph examples: Test 1 Sub 1 _ 3 Graphs png and Test 1 Sub 2 _ 6 Graphs png
Example of all data graphs: All Data Graphs png
Without the use of Slicers, the Graphs just show all the data on one graph, which means nothing.
I cannot visualize or google my way around the problem.
If it were an If statement:
If (trend is more than a certain percentage more than the last reading, for 2 consecutive readings) Then
Show the Slicer for that Test and Sub Test Headings
End If
That way, if there are more than 1 trends of data going toward failure, it would show them both for user analysis.
For my example, this would mean the test that prints octal data would not be shown in the slicer. Or if Test 1 Sub 2 _ 6 kept a steady measurement, it would also not be shown. While Test 1 Sub 1 _ 3 would be shown by the slicer.
I might be looking at this wrong, with the slicers, but that method seems like a good idea.
Any help greatly appreciated.
July 16, 2010
Offline
Hi Dave,
Welcome to our forum!
Please try to upload your Excel file again as it didn't get attached. You need to click 'start upload' after selecting your file, then wait for the grey check mark beside the file size before clicking 'submit reply'.
Mynda
February 15, 2022
Offline
Thanks for noticing, I thought I had attached it.
February 15, 2022
Offline
The xlsx attachment is only accessible when you are logged in. I just viewed the page as a guest and it was not there. Now I am logged in I can see it.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Dave,
An easy way is to add a formula in your data source to compare the reading with the previous readings of the same test, the formula should return 2 values: Ok and Warning.
If you use these in a slicer, you will be able to see only those tests with a warning.
First 3 rows in your sample have the same date time: 13/02/2022 3:56:49 PM
Is this correct?
February 15, 2022
Offline
Thank you Catalin,
A test is comprised of sub-tests basically.
So in the sample, test 1 contains sub-tests:
0 1
1 3
2 6
Each time the test is run, the sub-tests show the measurements.
This is why I am having difficulties, trying to compare the readings on only the same sub-tests in each test.
But, the warning/ok addition sounds like a good idea. Just trying to make the comparison on each sub-test is still difficult for me though.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
ok, so we cannot rely on date time to identify previous readings, as they are the same for all sub tests? Only sub test ID we can use to clearly identify previous readings?
February 15, 2022
Offline
Yes, just the test and sub test IDs.
Thansk for spending time thinking about it. Its rattling my head.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Ok, here is what i think:
analyzing a trend in the source data is a bit unusual, trending towards failure is not a strong KPI, because I guess that a trend does not mean it will fail.
How is % Inside Tolerance calculated? Why not marking the tests that actually failed and display only those? If a measurement is off, the entire test-subtest series will be marked as Off.
Did that in the file attached, you can use the output in a graph with a slicer on the Check column.
1 Guest(s)
