HR Prateek Sharma

Day 9 - Dynamic Dashboard Development

DAY 9

📌 Overview

Day 9 focuses on building dynamic, live-connected HR analytics dashboards using front-end web technologies. Learn to create interactive dashboards that automatically update when backend data changes, using Google Sheets as a live database.

🎯 Key Learning Objectives

                    Front-End Development: Master HTML, CSS, and JavaScript for dashboard creation
Live Data Integration: Connect dashboards to Google Sheets for real-time updates
Data Visualization: Create multiple chart types for comprehensive analytics
KPI Design: Build effective key performance indicator cards
Responsive Design: Ensure dashboards work across devices

                

💻 Front-End Technologies Explained

1. HTML (HyperText Markup Language)

Purpose: Structure and content of web pages

Role in Dashboard: Creates the skeleton - containers, divs, tables, canvas elements for charts

<div class="kpi-card"> <h3>Total Employees</h3> <p class="value">100</p> </div>

Indian HR Context Example:

Creating a section for PF compliance tracking using HTML structure with proper semantic tags for better accessibility.

2. CSS (Cascading Style Sheets)

Purpose: Styling, layout, and visual design

Role in Dashboard: Colors, fonts, spacing, animations, responsive grid layouts

.kpi-card { background: linear-gradient(135deg, #40E0D0, #FFD700); padding: 25px; border-radius: 15px; box-shadow: 0 8px 20px rgba(0,0,0,0.2); transition: transform 0.3s; } .kpi-card:hover { transform: translateY(-5px); }

Indian Company Theme Example:

TCS uses blue (#0066CC), Infosys uses blue and orange. Your dashboard can use company-specific color schemes for branding consistency.

3. JavaScript

Purpose: Interactivity, data processing, and dynamic updates

Role in Dashboard: Fetch data, calculate KPIs, create charts, handle user interactions

// Fetch data from Google Sheets fetch(SHEET_URL) .then(response => response.text()) .then(data => { const employees = parseCSV(data); calculateAttritionRate(employees); updateDashboard(); }); // Calculate Attrition Rate function calculateAttritionRate(data) { const total = data.length; const attrition = data.filter(e => e.Attrition === 'Yes').length; return (attrition / total * 100).toFixed(2); }

🔄 Complete Workflow

Step 1: Learn

Create summary notes of HR analytics concepts, KPIs, and metrics to track

Step 2: Claude AI - Create Detailed Content

Use Claude to expand summaries into comprehensive learning materials with examples, code snippets, and Indian context

Step 3: Notion - Publish Detailed Notes

Organize and structure content in Notion for team collaboration and easy reference

Step 4: Claude AI - Convert to HTML

Transform notes into interactive HTML dashboards with live data connections

Step 5: Blogs - Paste HTML Code

Publish the HTML dashboard code to Blogspot or other blogging platforms

Step 6: Blog Publish

Make your dashboard publicly accessible and share with stakeholders

📊 Essential HR Dashboard Components

KPI Cards to Include:

                    👥 Total Employees: Current headcount
📉 Attrition Rate: Percentage of employees leaving
💰 Average Salary: Mean monthly income in INR
⏱️ Average Tenure: Years at company
⭐ Job Satisfaction: Average rating (1-5 scale)
⚖️ Gender Ratio: Male to Female percentage
🎓 Education Distribution: Qualification breakdown
🏢 Department Strength: Employees per department

                

Charts for Comprehensive Analysis:

                    Pie Chart: Department distribution, Education field
Bar Chart: City-wise employees, Age distribution
Stacked Bar: Attrition vs Active by department
Line Chart: Attrition trends over time
Doughnut Chart: Marital status, Job roles
Horizontal Bar: Job satisfaction scores

                

🔗 Google Sheets Live Integration

⚠️ Critical Setup: Your Google Sheet MUST be set to "Anyone with the link can view" for the dashboard to access data publicly.

URL Format for Live Data:

Original URL: https://docs.google.com/spreadsheets/d/SHEET_ID/edit#gid=GID_NUMBER Export CSV URL (Use this in dashboard): https://docs.google.com/spreadsheets/d/SHEET_ID/export?format=csv&gid=GID_NUMBER

JavaScript Libraries Required:

                    PapaParse: Parse CSV data from Google Sheets
Chart.js: Create beautiful, responsive charts
Fetch API: Native JavaScript for HTTP requests

                

Real-World Example - Wipro HR Dashboard:

Wipro's HR team maintains a Google Sheet with 5,000+ employee records across Bangalore, Hyderabad, and Pune offices. The dashboard updates every hour automatically, showing:

Real-time headcount by location
Monthly attrition trends
Recruitment pipeline status
Training completion rates

🎨 Design Best Practices

                    Color Psychology in Dashboards:
                    Turquoise (#40E0D0): Trust, clarity, professionalism
Gold (#FFD700): Success, achievement, premium quality
Red: Alerts, high attrition, critical issues
Green: Positive metrics, growth, satisfaction

                

                    Responsive Design Principles:
                    Use CSS Grid or Flexbox for flexible layouts
Set breakpoints for mobile (768px), tablet (1024px), desktop
Ensure charts remain readable on small screens
Test on actual devices (iPhone, Android, iPad)

                

/* Responsive Grid Layout */ .kpi-container { display: grid; grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); gap: 20px; } @media (max-width: 768px) { .kpi-container { grid-template-columns: 1fr; } }

💡 Indian HR Context Examples

Example 1: TCS Bangalore Office Dashboard

Dataset: 2,500 employees

Key Metrics Tracked:

Attrition Rate: 11.2% (Industry avg: 15-18%)
Average Salary: ₹8,50,000 per annum
Gender Ratio: 32:68 (Women:Men) - Working towards 50:50
Average Age: 29.5 years
Projects across verticals: Banking (40%), Healthcare (25%), Retail (35%)

Dashboard Updates: Real-time via Google Sheets API, refreshes every 30 minutes

Example 2: Startup HR Analytics (50-person team)

Company: EdTech startup in Gurugram

Dashboard Features:

Stock options tracking for retention
Work-from-home vs Office attendance patterns
Employee NPS score: 8.2/10
Hiring velocity: 5 new hires per quarter
Cost per hire: ₹85,000 (including recruitment fees)

Example 3: Manufacturing Company - Chennai

Company: Automotive parts manufacturer, 800 employees

Unique Metrics:

Blue collar vs White collar ratio: 70:30
Shift-wise attendance tracking (3 shifts)
Safety training completion: 95%
Contract vs Permanent: 40:60
Tamil Nadu labour compliance dashboard

✅ Practice Tasks

Task 1: Create Basic HTML Dashboard Structure

Build a simple HTML page with 6 KPI cards showing: Total Employees, Attrition Rate, Average Salary, Gender Ratio, Average Age, and Department Count. Use semantic HTML5 tags.

Task 2: Style with CSS

Apply turquoise and gold theme to your dashboard. Create hover effects on KPI cards, add gradients, and ensure responsive design for mobile devices.

Task 3: Add JavaScript Data Processing

Write JavaScript functions to calculate:

Attrition rate from raw data
Average salary by department
Gender distribution percentage
Age group categorization (20-30, 31-40, 41-50, 51-60)

Task 4: Connect to Google Sheets

Create a Google Sheet with sample HR data (minimum 50 records). Make it public and integrate it with your dashboard using PapaParse library. Verify that changing data in the sheet updates your dashboard.

Task 5: Create Three Different Charts

Using Chart.js, create:

Pie chart for department distribution
Bar chart for city-wise employee count (Delhi, Mumbai, Bangalore, Hyderabad, Chennai, Pune, Kolkata)
Line chart showing monthly attrition trend

Task 6: Build a Complete Dashboard

Combine all elements into one comprehensive dashboard with:

Header with company branding
8 KPI cards
6 different chart types
Employee data table (top 20 records)
Refresh button for manual updates
Footer with credits

Task 7: Indian Compliance Dashboard

Create a specialized dashboard tracking Indian HR compliance:

PF deductions by month
ESI applicability tracking
Professional Tax by state
Gratuity eligibility count (5+ years tenure)
Form 16 generation status

❓ FAQs for HR Analytics Professionals

Q1: Can I use Excel files directly instead of Google Sheets?

Yes, but Excel files need to be hosted somewhere accessible via URL. Google Sheets is preferred because it provides a direct CSV export URL and supports real-time updates. Alternatively, you can use libraries like SheetJS to parse uploaded Excel files, but they won't auto-update.

Q2: How do I ensure data security when making sheets public?

Best practices:

Never include sensitive PII (Aadhaar, PAN, personal phone numbers)
Use employee IDs instead of names if possible
Anonymize or aggregate salary data
Consider using Google Apps Script to create a filtered view that only exposes necessary columns
For truly sensitive data, implement backend authentication (requires server-side code)

Q3: What's the maximum data size Google Sheets can handle?

Google Sheets limits:

Maximum 10 million cells total
Up to 40,000 rows recommended for good performance
For larger datasets (100,000+ employees), consider Google BigQuery or a proper database
Dashboard rendering may slow down with 10,000+ records - implement pagination or filters

Q4: How often does the dashboard update automatically?

By default, data is fetched only when the page loads. To enable auto-refresh:

Add a JavaScript setInterval function (e.g., every 5 minutes)
Balance between freshness and API rate limits
Google Sheets doesn't have strict rate limits for public sheets, but avoid refreshing every second
Include a manual refresh button for immediate updates

Q5: Can I add filters to the dashboard (by department, city, date range)?

Absolutely! Add dropdown menus or filter buttons using JavaScript:

Create filter UI elements (dropdowns, date pickers)
Write JavaScript functions to filter the dataset
Recalculate KPIs based on filtered data
Update charts with new filtered data
Example: Filter by "Department = IT" to see IT-specific metrics

Q6: What are Chart.js alternatives?

Popular charting libraries:

Recharts: React-based, modern, declarative
D3.js: Most powerful, steep learning curve
Plotly: Interactive, supports 3D charts
Google Charts: Easy integration with Google ecosystem
ApexCharts: Modern, feature-rich, good for real-time data

Chart.js is recommended for beginners due to simplicity and good documentation.

Q7: How do I handle Indian date formats (DD/MM/YYYY)?

JavaScript Date handling for Indian format:

// Convert DD/MM/YYYY to Date object function parseIndianDate(dateStr) { const [day, month, year] = dateStr.split('/'); return new Date(year, month - 1, day); } // Format for display const options = { day: '2-digit', month: '2-digit', year: 'numeric' }; const indianDate = date.toLocaleDateString('en-IN', options);

Always store dates in ISO format (YYYY-MM-DD) in your database for consistency.

Q8: What's the difference between front-end and back-end in HR dashboards?

Front-End (What we built today):

Runs in the user's browser
HTML, CSS, JavaScript
Handles display, user interactions, simple calculations
Limited security (anyone can view source code)

Back-End (Advanced, future learning):

Runs on a server
Languages: Python (Flask/Django), Node.js, Java
Handles databases, authentication, complex processing
Secure (code not visible to users)

For small teams (under 500 employees), front-end only dashboards work fine!

Q9: How can I make my dashboard accessible on mobile phones?

Mobile optimization checklist:

Use responsive CSS (flexbox, grid, media queries)
Set viewport meta tag: <meta name="viewport" content="width=device-width, initial-scale=1.0">
Stack KPI cards vertically on small screens
Reduce chart sizes for mobile (Chart.js responsive option)
Use touch-friendly buttons (minimum 44x44 pixels)
Test on actual devices: iPhone SE, Samsung Galaxy, iPad

Q10: What skills should I learn next after mastering basic dashboards?

Progressive learning path:

Immediate (Weeks 2-4): Advanced Chart.js, CSS animations, JavaScript ES6+
Short-term (Months 2-3): React.js for complex UIs, Git for version control
Medium-term (Months 4-6): Python for data analysis (Pandas, NumPy), SQL for databases
Long-term (Months 7-12): Machine learning for predictive analytics (attrition prediction), Cloud deployment (AWS,

Search This Blog