QHO539: Analytics & Business Intelligence

QHO539: Analytics & Business Intelligence

Student Name:

Student ID:

Introduction

The Analytics Business Intelligence unit is intended to provide an understanding of various analytical methods, tools, and techniques for data analysis and decision-making for business organizations. The primary goal of the unit is the mastery of skills to assess, process, and communicate results based on the assessment through the application of multiple analytical instruments and approaches. After completing the course, students learn about various roles involved in data science, the data handling methodology, and statistical tools applicable when solving business problems.

The embedded PLR is a form of reflective diary where the learning progress done throughout the module is recorded (gov. uk, 2018). The main purpose of the PLR is to present a documented description of the tasks accomplished with an indication of how the main methodologies; descriptive as well as comparative analysis, data displaying structures, and simple statistical analysis methods have been utilized. Hence, the report has a goal of providing examples of how the learning acquired has been applied to solve actual business problems.

Techniques employed in the conduct of this PLR include the Formation of a Pivot Table, Over-reliance on Excel Analysis functions, besides utilization of bar charts and histograms in portraying findings. These methods are systematically applied to weekly logs and targeted question analysis and enhance the main learning achievements stressed in the module.

Weekly Logs

The following weekly logs are about working only within the scope of the MentalHQ2020 dataset and the approaches that were applied to answer Questions 47 and 48. All the logs follow a systematic way of handling the dataset with an emphasis on extracting knowledge regarding increased rates of mental disorders across age groups and efficiency between the public and private sectors.

Week 1: Understanding the MentalHQ2020 Dataset and Setting Up Data Framework

During the first week, information about the structure of the MentalHQ2020 dataset and its variables was preliminarily analyzed to build up a further study. This was done by opening the data set in Excel and analyzing the variables that deal with patient characteristics, healthcare management progression and the mental health statuses of the subjects.

The most significant move was to develop a data dictionary that identifies the type of each record’s variables they contain (e.g., dob, age, mental health history, treatment care pathway). Some pre-processing works were done for initial data cleaning to manage the missing values and wrong type of values in the variables like converting the DOB data into age at ‘Age’ variable through the Excel formula DATEDIF function (Cheusheva, 2024). This step was crucial so that if age-related analysis had to be made on the participants (Question 47), it could be done.

Output: The creation of cleaned and derived age columns and the generation of a data dictionary for further data processing in the following weeks easier to filter and analyze.

Week 2: Creating Age Groups and Initial Data Analysis for Question 47

In the second week, the emphasis was placed on the segmentation of the dataset to compare the frequency of mental illnesses among young people and patients of old age (Question 47). The empirical approach was applied through a series of Excel formulas known as IF and VLOOKUP to allocate patients into pre-established age categories (<20, 20-29, 30-39, 40-49, etc.).

The process entailed the use of a pivot table to analyze the frequency occurrence of entries under variable mental health history based on various age subgroups (Kirtley and O'Mahony, 2023). The data was compared by counting and then expressing as percentages the number of patients diagnosed with a history of mental health problems in each group.

Output: To present the results of the analysis of the distribution of cases by age, a 3-D Clustered Column chart was used. Among the age groups, 30-39 had the highest mental health caseload suggesting a trend that needs further exploration.

Week 3: Analyzing Healthcare Pathways and Readmissions for Question 48

In the third week, the topics for discussion included the comparison of public and private routes to health care (Question 48). The methodology included subsetting the treatment care pathway column to include entries that match important kinds of healthcare referrals like Hospital referrals, Traditional and Others. That is why the total number of readmissions for each of the pathways must be counted to quantify the results of the treatment and look at the effectiveness concerning the given rates of readmissions.

The idea of doing this was to create a pivot table that would show how many patients were readmitted under each type of healthcare pathway. The findings were then cumulative and presented in terms of the total readmissions percentage.

Output: A pie chart was created to best depict the breakdown of readmission rates by care pathways. The chart also showed that Hospital referrals contributed most to the readmissions (69.23%); the author recommended that hospital-based mental health care outcomes should be reviewed (Microsoft.com, 2021).

Week 4: Visualizing Data Trends and Building Charts

During the fourth week, the emphasis was placed on constructing proper visuals to assist with the conclusions of the prior weeks. To compare the distribution of mental health cases and the readmission rates, used bar charts and clustered column charts, and to compare the readmission rates only, the pie chart. Has been used to develop such dynamic visual charts, Excel’s PivotChart facility was employed and such charts could be filtered based on certain variables such as age, healthcare pathway and the history of mental ailment.

The age-related trends for Question 47 were presented in the form of a 3-D Clustered Column and a Pie Chart was used to represent the graph for Question 48 where the recommendations are about the proportion of readmissions by healthcare pathway. The new taxonomic features that were placed included color-coding in addition to new labels to make the appearance much clearer and easier to read.

Output: Course visualization 47 and 48 in the form of a visual dashboard with sub-charts. Another advantage of using the dashboard is the ability of those who need it to directly manipulate the trends by themselves, explore the areas of interest in the context of the results they have been given, discuss these results with the stakeholders, and identify additional areas of analysis.

Week 5: Drafting the Final Report and Preparing for Future Analysis

In the fifth week, the emphasis was placed on presenting the structure of the final report and discussing the detailed examination of the data obtained during the creation of the MentalHQ2020. This included the synthesis of responses to Questions 47 and 48, descriptions of research approaches utilized, and descriptions of subsequent steps to account for all other questions.

Judging from the data, it was ascertained that the age brackets of less than thirty had more mental health complications than the older age groups, although hospital referrals reduce the probability of readmissions. These insights were the starting point for proposing additional research into factors of health care management and mental health services for different age groups.

Output: The layout of the final report with subheadings of Sections to be developed under the heading of Introduction, Methodology, Results and Recommendations. The report offered good guidelines for how to lay down the results following a sequential procedure to coincide with the goals of the Personal Learning Record (Creately.com, 2024).

These weekly logs are systematic and expand from the MentalHQ2020 dataset as analyzed, with emphasis on the target questions. All the activities within a week provide the base for continuity and the improvement of comprehension of Mental health trends and Healthcare results.

Analysis of Target Questions

Introduction to Target Questions Analysis

Specifically, 2 questions are addressed by the analysis: the correlation between age factor and the rates of disclosed mental illnesses (Question 47) and the comparative efficiency of public and private healthcare systems concerning the rates of readmission to healthcare facilities (Question 48). The results are then obtained employing Excel; they involve data manipulation and use simple and comprehensible graphs.

Question 47: Are younger people more likely to develop mental illnesses compared to older patients?

Objective

It concerns the intention of this question to establish whether there is a correlation between age and the prevalence of mental health problems by comparing the data generated for young people with that generated for older patients. Conceptualizing age and mental disorder can play a vital role in the development of age-appropriate mental health interventions and policies.

Methodology

The analysis was done using the MentalHQ2020 data set, variables which are dob, and the variable mental health history- whether the patient suffers from a mental illness or not. The first thing was to determine the age of each of the patients from the Date of Birth. To calculate the age of each patient Excel’s DATEDIF function was used and from which the age was derived.

The next step was to group the patients by age to help in the most accurate comparisons. The age categories included pediatric (< 20 years), young adult (20-29 years), adults 30-39 years, 40-49 years, 50-59 years, 60-69 years, and 70-79 years. This categorization provided the basis for a structured comparison of the mental health prevalence across different age cohorts.

For this case, a pivot table was developed in Excel where age served as the row field whereas mental health history served as the value field. Such design facilitated easy identification of patient numbers within given age classes having a prior history of mental disorders. These were followed by a 3-D Clustered Column chart that depicted the frequency distribution of cases of mental health by age.

Age Group	Count of mental health history
<20	45
20-29	98
30-39	215
40-49	92
50-59	31
60-69	14
70-79	2
Grand Total	497

Results & Discussion

The analysis of the results shows an obvious increase in mentally ill people in the following age groups. Based on the age group incorporation, the 30-39 years presented the most common cases of mental health problems than the 20-29 years group. Surprisingly though, countries showed a low prevalence of cases of mental health problems with a clear trend that the older age brackets, especially over fifty years depicted lower statistics in the various countries. Such a pattern might indicate that patients under the age of 40 face more mental health issues than 40-and-older patients.

These observations were complemented by the analysis of the 3-D Clustered Column chart, based on which it is possible to state for sure, that the rate of mental health problems is higher among people in the 30-39 age group. This trend could be due to factors like increased stress levels, work-related stressors, and social pressures, which are more common among people within this age bracket. On the other hand, reduced outcomes of mental health problems in the elderly could be attributed to reduced exposure to the identified stressors or reduced prevalence of reporting mental health problems among elders (Ucdavis.edu, 2024).

In total, the study provided evidence that age is an important predictor of mental health problems in both younger and middle-aged adults. It could help to design future campaigns for chronologically segregated prevention and intervention.

Question 48: How effective is public healthcare over private in terms of how many patients are readmitted through private healthcare compared to public healthcare?

Objective

In this case, it is hoped that a comparative analysis of public and private systems, according to patient recurrence will be possible. Such a comparison assists in comparing which type of healthcare has a shorter time of managing mental health cases and fewer readmissions (Western Tidewater Community Services Board, 2024).

Methodology

The present study employed data from the MentalHQ2020 dataset with the treatment care pathway and ever-admitted mental health variables. The treatment column gave details on the kind of healthcare path that belonged to each patient including family reference, hospital reference, or traditional. According to the type of treatment modality applied in each healthcare path, they were classified as either Public or Private (Indeed Career Guide, 2024).

For readmission data, the ever-admittedformentalhealth column was used to determine if patients had been admitted for mental health problems again or not. Healthcare pathways were then tabulated as follows with a pivot table to determine the number of readmissions for each. Descriptive comparative data were presented in terms of counts and percentages of readmissions through different pathways.

To begin with, while providing directions on how to answer the directions, a 100% Stacked Column Chart was proposed for presenting the readmission rates. However, a Pie Chart was found to be more suitable to highlight the proportion of readmissions among the healthcare pathways because they are few and because the Pie Chart provides a clear picture of the relative effectiveness of such pathways.

	Column Labels
	Yes		Total Count of everadmittedformentalhealth	Total Count of everadmittedformentalhealth2
Row Labels	Count of everadmittedformentalhealth	Count of everadmittedformentalhealth2
Hospital referral	9	69.23%	9	69.23%
Other	2	15.38%	2	15.38%
Traditional	2	15.38%	2	15.38%
Grand Total	13	100.00%	13	100.00%

Results & Discussion

From the pie chart, it was evident that Hospital referral was leading in the readmission rates at 69.23%. This high rate of readmission once again raises questions about discharge planning, aftercare support necessary mental healthcare in the hospital possibly, expanding the nature of treatment regimens, out of which Traditional and Other had higher readmission rates at 15.38% each, thus making them potentially more efficient in preventing individuals with recurring mental health problems or patients with less severity of the condition.

The results suggest that hospital referral for public healthcare is weaker than private/other/traditional care options in reducing mental health rehospitalization. A high incidence of hospital readmissions could be attributed to diverse reasons ranging from the acuity of illnesses that patients are treated within hospitals, to lack of outpatient follow-ups or constraints in available public health resources.

With this knowledge, it is imperative that more work look at hospital-based mental health care with consideration to ways that these could be developed. On the other hand, the results obtained from the lower readmission rates found in the private or alternative care pathways may mean that these systems do have more efficient patient management and follow-up, and thus more sustainable long-term solutions.

Thereby, analysis emphasizes the primary area of research regarding healthcare pathways and patient readmissions, and public and private mental health services.

A cumulative understanding of two target questions is derived from these analyses concerning the prevalence of mental health disorders and the efficiency of public and private healthcare chains across different ages.

Conclusion & Reflection

The analysis conducted throughout this report has shed light on the important realities concerning mental health by factoring in different aspects of various health systems. The weekly logs demonstrated the significance of comprehending organizational theories and data science topics, as well as data manipulation and visualization procedures that were useful in tackling the two focal queries.

The outcome of the analysis for Answer 47 indicated that the majority of individuals with mental health troubles are young and middle aged especially those from 30 to 39 years. They point towards the importance of introducing mental health programs directed at these particular age groups. For Question 48, it was found that measures of hospital-based (public) healthcare systems had significantly higher readmission rates than other healthcare choices and therefore indicate the need for general changes in public healthcare systems.

Looking at the learning activity outcome, the tasks facilitated sharpening analytical skills, broadened knowledge of Excel options, and fine-tuned skills of data interpretation and use. An increase in the competence in the management of large data sets and modeling of results given by the structured approach to this course is consistent with the learning outcomes for the unit.

In the future, a more complex model may consider extra vital variables such as SES or disease severity in mental health or evaluate the frequencies of other illnesses providing even more intricate results on mental health and healthcare efficiency.

References

Cheusheva, S. (2024). How to calculate age in Excel from birthday. [online] ablebits.com. Available at: https://www.ablebits.com/office-addins-blog/calculate-age-excel/ [Accessed 2 Oct. 2024].

Creately.com. (2024). Weekly Work Log Template | Creately. [online] Available at: https://creately.com/diagram/example/ZpsOPOhshrC/weekly-work-log-template [Accessed 2 Oct. 2024].

gov.uk (2018). Accessing your personal learning record. [online] GOV.UK. Available at: https://www.gov.uk/guidance/how-to-access-your-personal-learning-record [Accessed 2 Oct. 2024].

Indeed Career Guide. (2024). How To Write a Methodology (With Tips and FAQs). [online] Available at: https://www.indeed.com/career-advice/career-development/how-to-write-a-methodology [Accessed 2 Oct. 2024].

Kirtley, J. and O'Mahony, S. (2023). What is a pivot? Explaining when and how entrepreneurial firms decide to make strategic change and pivot. Strategic Management Journal, 44(1), pp.197-230. https://www.academia.edu/download/79296030/WhatIsAPivot_KirtleyOMahony_09202018.pdf

Microsoft.com. (2021). Add a pie chart - Microsoft Support. [online] Available at: https://support.microsoft.com/en-us/office/add-a-pie-chart-1a5f08ae-ba40-46f2-9ed0-ff84873b7863 [Accessed 2 Oct. 2024].

Ucdavis.edu. (2024). EXCEL Charts: Column, Bar, Pie and Line. [online] Available at: https://cameron.econ.ucdavis.edu/excel/ex12charts.html#:~:text=Column%20chart%3A%20for%20comparing%20data,two%20series%20(given%20later). [Accessed 2 Oct. 2024].

Western Tidewater Community Services Board. (2024). What’s the Difference Between Public and Private Behavioral Health Care? | WTCSB. [online] Available at: https://www.wtcsb.org/whats-the-difference-between-public-and-private-behavioral-health-care/ [Accessed 2 Oct. 2024].

?

Appendices

1) Does employment status affect overall mental health?
Row Labels	Count of mentalhealthhistory	Count of mentalhealthhistory2
Agriculture, Food and Natural Resources	7	1.36%
Architecture and Construction	12	2.33%
Arts, Audio/Video Technology and Communications	18	3.50%
Business Management and Administration	46	8.95%
Education and Training	16	3.11%
Finance	59	11.48%
Government and Public Administration	37	7.20%
Health Science	109	21.21%
Hospitality and Tourism	15	2.92%
Human Services	27	5.25%
Information Technology	23	4.47%
Law, Public Safety, Corrections and Security	20	3.89%
Manufacturing	2	0.39%
Marketing, Sales and Service	29	5.64%
Science, Technology, Engineering and Mathematics	25	4.86%
Transportation, Distribution and Logistics	16	3.11%
Unemployed	53	10.31%
Grand Total	514	100.00%

2) Does growing up without parents have any effect on mental health?
Count of mentalhealthhistory	Column Labels
Row Labels	No	Yes	Grand Total
Both parents	1		1
in care home	1	3	4
on the street		1	1
with both parents	268	43	311
with father	23	2	25
With mother	103	24	127
with relatives	53	8	61
Grand Total	449	81	530

4) Is mental illness partly genetic?
	Column Labels
	Yes		Total Count of mentalhealthhistory	Total Count of mentalhealthhistory2
Row Labels	Count of mentalhealthhistory	Count of mentalhealthhistory2
Anxiety & panic attacks	2	2.47%	2	2.47%
Anxiety & panic attacks;Bipolar disorder	1	1.23%	1	1.23%
Anxiety & panic attacks;Bipolar disorder;Depression	1	1.23%	1	1.23%
Anxiety & panic attacks;Bipolar disorder;Depression;Eating disorders;Obsessive-compulsive disorder;Personality disorders;Post Traumatic Stress Disorder (PTSD);Psychosis;Self harm;Suicidal feelings;Attention deficit hyperactivity disorder;Autism	1	1.23%	1	1.23%
Anxiety & panic attacks;Bipolar disorder;Depression;Obsessive-compulsive disorder;Suicidal feelings	2	2.47%	2	2.47%
Anxiety & panic attacks;Depression	7	8.64%	7	8.64%
Anxiety & panic attacks;Depression;Eating disorders	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Eating disorders;Personality disorders;Attention deficit hyperactivity disorder	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Eating disorders;Personality disorders;Post Traumatic Stress Disorder (PTSD)	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Eating disorders;Post Traumatic Stress Disorder (PTSD);Schizophrenia;Self harm;Suicidal feelings	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Eating disorders;Self harm;Suicidal feelings;Attention deficit hyperactivity disorder;Autism	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Eating disorders;Suicidal feelings	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Obsessive-compulsive disorder;Personality disorders;Post Traumatic Stress Disorder (PTSD);Schizophrenia;Self harm;Suicidal feelings;Autism	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Obsessive-compulsive disorder;Post Traumatic Stress Disorder (PTSD)	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Obsessive-compulsive disorder;Post Traumatic Stress Disorder (PTSD);Schizophrenia;Suicidal feelings;Other	2	2.47%	2	2.47%
Anxiety & panic attacks;Depression;Personality disorders;Post Traumatic Stress Disorder (PTSD)	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Post Traumatic Stress Disorder (PTSD);Attention deficit hyperactivity disorder;Autism	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Post Traumatic Stress Disorder (PTSD);Self harm;Other	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Post Traumatic Stress Disorder (PTSD);Self harm;Suicidal feelings	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Self harm;Suicidal feelings	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Self harm;Suicidal feelings;Attention deficit hyperactivity disorder;Autism	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Suicidal feelings;Attention deficit hyperactivity disorder	1	1.23%	1	1.23%
Anxiety & panic attacks;Depression;Suicidal feelings;Autism	1	1.23%	1	1.23%
Anxiety & panic attacks;Eating disorders;Self harm;Suicidal feelings	1	1.23%	1	1.23%
Anxiety & panic attacks;Post Traumatic Stress Disorder (PTSD)	1	1.23%	1	1.23%
Attention deficit hyperactivity disorder;Autism	1	1.23%	1	1.23%
Autism	1	1.23%	1	1.23%
Bipolar disorder;Depression;Schizophrenia	1	1.23%	1	1.23%
Depression	10	12.35%	10	12.35%
Depression;Attention deficit hyperactivity disorder	1	1.23%	1	1.23%
Depression;Eating disorders	1	1.23%	1	1.23%
Depression;Other	1	1.23%	1	1.23%
Depression;Post Traumatic Stress Disorder (PTSD)	1	1.23%	1	1.23%
Depression;Self harm;Suicidal feelings	4	4.94%	4	4.94%
None	20	24.69%	20	24.69%
Other	2	2.47%	2	2.47%
Schizophrenia	1	1.23%	1	1.23%
Schizophrenia;Attention deficit hyperactivity disorder	1	1.23%	1	1.23%
Suicidal feelings	1	1.23%	1	1.23%
(blank)	1	1.23%	1	1.23%
Grand Total	81	100.00%	81	100.00%

5) Can suicidal thoughts be more influenced if a close family member has passed away from it?
Row Labels	Count of sucideinfamily	Count of sucideinfamily2
No	436	85.49%
Yes	74	14.51%
Grand Total	510	100.00%

6) Could relationship status affect your mental health?
Row Labels	Count of mentalhealthhistory
Divorced	39
married	283
never married	179
Prefer not to say	25
widow	3
(blank)	2
Grand Total	531

10) What job occupation has the highest number of employees with mental health problems?
Row Labels	Count of mentalhealthtype
Agriculture, Food and Natural Resources	3
Architecture and Construction	1
Arts, Audio/Video Technology and Communications	2
Business Management and Administration	4
Education and Training	3
Finance	5
Government and Public Administration	8
Health Science	17
Hospitality and Tourism	3
Human Services	6
Information Technology	1
Law, Public Safety, Corrections and Security	1
Manufacturing
Marketing, Sales and Service	8
Science, Technology, Engineering and Mathematics	4
Transportation, Distribution and Logistics
Unemployed	11
Grand Total	77