A lifelong learner, practitioner, and sharer committed to the path of technology, an original blogger who is busy and sometimes lazy, and a teenager who is occasionally boring and sometimes humorous.
Welcome to search “Jge’s IT Journey” on wechat!
“Luo Yonghao douyin Debut” sales data visualization screen is how to make?
Luo made his first live broadcast on Tiktok on April 1 last year, attracting more than 30 million viewers and ultimately exceeding 110 million sales. Then a “visualized large screen display” of the live broadcast data was released online. Many people were curious about what it was made of. Today, I will take you to make a visualized large screen similar to the following picture.
The above picture is the large screen visualization of Luo Yonghao’s live data, and the following picture is the large screen visualization we want to do in this article. First, let’s show you a video! Exactly the same, interested can follow me to operate again.
I. ERP system (Enterprise Resource Planning)
Recourse The resource ratio is applied to enterprises. It is called “ERP System”, the full name of which is “EnterPrice Recourse Planning”. Take Recourse company xx for example.
1) Procurement management: they can’t do anything like CPU, motherboard and chip, so they need to purchase. 2) Production management: mainly look at the process, as well as the qualified rate. 3) Inventory management: according to the market demand, which products need more inventory and which need less inventory to ensure the balance of inventory supply. 4) Sales management: online and offline sales. 5) Logistics management 6) Financial management: such as incoming, outgoing, budget, etc. 7) Human resources management: employee relations. 8) Corporate culture: Different companies have different corporate cultures.
Second, the HRP
When ERP system is applied to medical industry, it becomes “HRP”, full name is “Hospital Resource Planning”, also called “Hospital Resource Planning”.
Iii. Project background and purpose
Based on some data of a hospital, this paper makes a “cockpit” for the director to help him understand the operation situation of the hospital and the existing problems, and to diagnose these problems, so as to achieve the rationalization of resource allocation.
4. Project requirements
From the above purposes, we can know that this paper is to make a large visual screen for the dean to help him make decisions. Since it is to help the dean, we need to know what we should focus on as a dean, and what he pays attention to is what we should do. Here are some of them:
- The staffing ratio of various departments, such as doctors, nurses and patients.
- Medical treatment of each department, outpatient and inpatient (hospital bed)
- Revenue of each department;
5. Introduction of data table
Vi. Software implementation
6.1 Creating a Decision report, add a background
6.2 Connecting to the database: hospital_hrp
6.3 Production of title and department list map
6.3.1 Creating a Data Connection and Importing a Data source
6.3.2 Importing a Report Block: Adding a Title (Resource Ratio Decision Screen)
6.3.3 Click to edit and perform the following operations
6.3.4 After the Settings are saved, preview the effect
6.3.5 Import a report block again for making department list diagram
6.3.6 Click the above to complete the following operations
6.3.7 After adjusting the spacing between two report blocks, save and display the effect
6.4 Personnel ratio analysis of each department
- National standard configuration indicators: doctor-patient ratio (1:5), nursing-patient ratio (1:2.5), which is the national standard configuration.
- Requirement 1: Monthly trend analysis of the ratio of doctors, nurses and patients in a certain department
- Requirement 2: Comparative analysis of national standard configuration (proportion) and hospital status (proportion) in a certain department
6.4.1 Requirement 1: Monthly trend analysis of the ratio of doctors, nurses and patients in a certain department
Using SQL statement, the number of doctors and nurses in different departments are counted
"-- -- -- -- -- -- -- -- the doctor count -- -- -- -- -- -- -- -- -- --" select deptno, the deptname, Select count(name) from doctor group by deptno,deptname "-------- Select count(name) from nurse group by deptno,deptname "-------- count(name) from nurse group by deptno, deptName "-------- select count(name) from nurse group by deptno, deptName "----------" select deptno,in_month, Count (name) Number of patients from hospital group by deptno,in_monthCopy the codeHere are the results:
Make a table join to get the data we use for plotting
Select a. d. eptname in_month, number (the number of patients/doctor) as doctor-patient ratio, number (the number of patients/nurse) as than the from nurses and patients (select deptno, the deptname, Count (name) from doctor group by deptno,deptname) a, (select deptno,deptname, Select count(name) from nurse group by deptno,deptname) b, (select deptno,in_month, Count (name) Number of patients from hospital group by deptno,in_month) c where A.eptno = B.eptno and A.eptno = C.eptnoCopy the codeThe results are as follows (the truncated results are as follows) :
In sail soft, use the above SQL statement, get the final drawing data
Insert a line chart
Click on the edit above to bind the data source
Setting the title (critical)
Because we need to set the linkage effect in the future, that is to say, which department you choose, the title should show which department, this step is very critical. And just to be clear, I’m not going to talk about it for the rest of the statement.
Add two red lines (also called guides)
We know that the ratio of the country: doctor-patient ratio (1:5), nursery-patient ratio (1:2.5), so take 2.5 and 5 as the criterion, each make a reference line, can make a clear comparison between the current situation of the hospital and this reference line.
Note: Similarly, we can add a nursing-patient ratio trend line again, the operation procedure is the same, do not demonstrate here.
After the operation is saved, the effect is displayed
Note: After each operation, it is best to save it.
The problem comes: at this time, the department table and the lower line chart can not be linked, so what to do?
Setting of linkage effect between department table and line chart
First, we edit this “department table”
When the following page is displayed, complete the following operations.
When the following page is displayed, complete the following operations.
After saving, the effect is displayed.
6.4.2 Demand 2: Comparative analysis of national standard configuration (proportion) and hospital status (proportion) in a certain department
Use SQL statement to get “Proportion of national standard configuration in a certain department”
SELECT * FROM 'normalConfig' WHERE deptname='${department name}'Copy the codeThe operation is as follows:
Insert a pie chart and select “Torus – Old version” and select stereoscopic torus
Bind the data source
Set the title (why is explained above)
Note: I actually set some other parameters as well, it’s very simple, we won’t show it here
Then, we set the linkage effect of the “department table” and the ring diagram again
First, we edit this “department table”
When the following page is displayed, complete the following operations.
When the following page is displayed, complete the following operations.
After saving, the effect is displayed.
Similarly, we can make “proportion of standard allocation in the current situation of the hospital of a certain department”. Since most of the steps are the same, only different steps are shown here. Only the “bind data source” step is different.
But not at this point. Why not? Because the legend of our left and right circles is not one to one. At this point, we will “national standard configuration” of the ring diagram, the adjustment of today’s row field.
After saving, the effect is demonstrated.
6.5 Analysis of medical visits
- Demand 1: Comparative analysis of existing and standard beds in a department (bar chart)
- Demand 2: bed utilization
- Demand 3: Monthly trend analysis of hospital bed utilization in a department
6.5.1 Requirement 1: Comparative analysis of existing and standard beds in a certain department
Using SQL statement, the number of existing and standard beds were counted
Select a.deptname from (select deptno,deptname,bednum from normalConfig) a, (select a.deptname, bednum from normalConfig) a, (select a.deptname, deptName,bednum from normalConfig) a, (select deptno,deptname,bednum as existing from department) b where a.eptno = B.eptnoCopy the codeThe result is as follows (the truncated part is as follows) :
In sail soft, use the above SQL statement, get the final drawing data
Insert a bar chart and bind the data
Set the title
Dynamic flicker effect display: special effect function
When a value is negative, it is marked red.
Then, we set the linkage effect of “department table” and bar chart again
First, we edit this “department table”
When the following page is displayed, complete the following operations.
When the following page is displayed, complete the following operations.
After saving, the effect is displayed.
6.5.2 Demand 2: Bed utilization
Bed utilization = number of patients/number of beds per month (0.7-0.9 is good)
Using SQL statement, statistics of hospital bed utilization standard departments
Select deptname, in_month, round((select * from department) a, (select * from department) a, (select deptno,in_month, Count (name) as count from hospital group by deptno,in_month) b where a.deptno = B.deptno and (count /bednum) >= 0.7 and count(name) as count from hospital group by deptno,in_month) b where A.deptno = B.deptno and (count /bednum) >= 0.7 and (Number of patients /bednum) <= 0.9Copy the codeHere are the results:
In sail soft, use the above SQL statement, get the final drawing data
Insert a bar chart and bind the data
After saving, the effect is displayed
6.5.3 Demand 3: Monthly trend analysis of hospital bed utilization in a department
Using SQL statement, the number of existing and standard beds were counted
Select deptname, in_month, round((select * from department) a, (select * from department) a, (select deptno,in_month, Count (name) as number of patients from hospital group by deptno,in_month) b where A.eptno = B.eptnoCopy the codeThe result is as follows (the truncated part is as follows) :
In sail soft, use the above SQL statement, get the final drawing data
Insert a bar chart and bind the data
Set the title
Add a guide: 0.85
Then, we set the linkage effect of “department table” and bar chart again
First, we edit this “department table”
When the following page is displayed, complete the following operations.
When the following page is displayed, complete the following operations.
After saving, the effect is displayed.
6.6 Income analysis
- Demand 1: Monthly trend analysis of inpatient income of a department
- Demand 2: bed utilization
6.6.1 Demand 1: Monthly trend analysis of inpatient income of a department
Using SQL statement, the number of existing and standard beds were counted
Select deptname,in_month from (select deptno,in_month, Round (sum(lastincome),2) from hospital group by deptno,in_month) a (select * from department) b where a.deptno = b.deptnoCopy the codeThe result is as follows (the truncated part is as follows) :
In sail soft, use the above SQL statement, get the final drawing data
Insert a bar chart and bind the data
Set the title
Then, we set the linkage effect of “department table” and bar chart again
First, we edit this “department table”
When the following page is displayed, complete the following operations.
When the following page is displayed, complete the following operations.
After saving, the effect is displayed.
Recommended reading
AB Testing for data Analysis
“Are you still working on files or folders manually?”
The most detailed Python combined with RFM model to achieve user stratified practical case!
Use Python to merge multiple sheets, merge multiple workbooks, and split a table by column
Original is not easy, if you think this article is a little useful to you, please give me a like, comment or forward for this article, because this will be my power to output more quality articles, thanks!
By the way, dig friends remember to give me a free attention yo! In case you get lost and you can’t find me next time.
See you next time!