Looking to 'visualize' errors based on a few conditions

[JediMole]

I have a data set that management wants to be able to visually see items out of 'compliance' based on a set of criteria:

Rules:

• If status is blank, that’s an error
• If dates are past, and status is proposal or pending, that’s an error
• If dates are 60 days past last day and the actuals are missing on a status of confirmed, that’s an error

I currently have the data in a matrix two ways to attempt to figure this out.  Whats the best way to highlight or present the data for quick look to see?

and\or

any insights how to best accomplish or vidoe links are appreciated!

[danextian]

In times like these, ChatGPT can be very helpful for generating ideas on how to visualize your data.

[JediMole]

Also, Im not quite sure how I would even ask chatgpt this question!

[JediMole]

Thats fair, and in this case because theres no real HIPPA type data in there I could do.  I wanted to see if a quick answer here would be available as a starting point as I cant use AI on my work system and need to port this over to personal system

[Ritaf1983]

Hi @JediMole 

I think this question might not be entirely suitable for the forum, partly because it requires a clear understanding of the user’s specific needs and who they are, and partly because it’s somewhat complex.
I'll try to provide general guidelines, and you can adapt them to fit your requirements.
Since you mentioned that this is intended for management, I believe that what’s more important to them is seeing the statuses and trends. As for row-level data, it is either less relevant or more relevant for operational personnel.
Therefore, the summary table should be positioned at the top, with its starting point being a ranking by statuses. Additionally, it should not only display the absolute number for each status but also the percentage these entries represent out of the total.
Something like this :

Regarding trends, my personal recommendation is not to present data from the beginning of the year but rather for the last 12 months. This way, managers will always see trends regardless of the current point in the year, and seasonality will also be reflected.
You can add trend lines using sparklines.

To overcome the fact that sparklines are small and lack point-level tooltips, you can add additional information using a few tricks, such as the option to view the line chart in a "zoomed-in" version as part of the tooltip.

Or display the minimum and maximum boundaries in a cell next to the sparkline.

And back to the detailed table.
If the row-level table is all that needs to be displayed in the report, it can be positioned below the summary table. This way, any click on a status in the summary table above will filter the detailed table to show only the rows relevant to that status

If this is not the only element on the report, you can move the detailed table to another page and use the drill-through functionality.
Here’s a guide to using drill-through:
https://www.youtube.com/watch?v=BbplhqDCWOM&t=10s

Apologies in advance if I didn’t cover all the details.
As I mentioned, questions about effective visualization at this level are somewhat too complex for the forum format.
I hope I managed to provide a framework that can help you move forward independently.
The files with the examples are attached.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[JediMole]

Thanks Rita!  Because the calendar is 'linear' in time, they want to see which events are out of comliance so the time stamp matters and has to be looked at in that fashion.  I'll watch the link you provided though and report back

[Anonymous]

Hi @JediMole 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster?

If not, could you please share your data(exclude sensitive data), or create some sample data, and your target outcome, so that we can help you better.

Thank you very much for your kind cooperation.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JediMole · Accepted solution]

I did figure it out and for the life of me I cant remeber what I did TBH...