Forum

Notifications
Clear all

Trend Analysis of Bulk Data

10 Posts
3 Users
0 Reactions
118 Views
(@exceluser123)
Posts: 6
Active Member
Topic starter
 

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.

All-Data-Graphs-1.PNGTest-1-Sub-1-_-3-Graphs-1.PNGTest-1-Sub-2-_-6-Graphs-1.PNG

 
Posted : 16/02/2022 8:17 am
(@mynda)
Posts: 4761
Member Admin
 

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

 
Posted : 17/02/2022 7:14 am
(@exceluser123)
Posts: 6
Active Member
Topic starter
 

Thanks for noticing, I thought I had attached it.

 
Posted : 17/02/2022 3:28 pm
(@exceluser123)
Posts: 6
Active Member
Topic starter
 

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.

 
Posted : 18/02/2022 7:54 am
(@catalinb)
Posts: 1937
Member Admin
 

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?

 
Posted : 19/02/2022 12:52 am
(@exceluser123)
Posts: 6
Active Member
Topic starter
 

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.

 
Posted : 19/02/2022 1:40 pm
(@catalinb)
Posts: 1937
Member Admin
 

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?

 
Posted : 20/02/2022 12:41 am
(@exceluser123)
Posts: 6
Active Member
Topic starter
 

Yes, just the test and sub test IDs.

Thansk for spending time thinking about it. Its rattling my head.

 
Posted : 20/02/2022 8:23 am
(@catalinb)
Posts: 1937
Member Admin
 

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.

 
Posted : 26/02/2022 1:42 am
(@exceluser123)
Posts: 6
Active Member
Topic starter
 

Its okay I think I managed it with slicers like you say. SOLVED.

 
Posted : 28/02/2022 10:49 am
Share: