Tables, Pivots & Charts
You have your dataset. Now make it visual.
Pick the Right Visualization
VISUALIZATION DECISION GUIDE
─────────────────────────────────────────────────────────────────
What do you need to show?
│
▼
┌────────────────────┐ ┌─────────────────────────────────┐
│ Detailed records? │────▶│ TABLE VIEW │
│ (drill-down data) │ │ Every row, sortable, filterable │
└────────────────────┘ └─────────────────────────────────┘
│
▼
┌────────────────────┐ ┌─────────────────────────────────┐
│ Summarized totals? │────▶│ PIVOT TABLE │
│ (grouped metrics) │ │ Group by category, show totals │
└────────────────────┘ └─────────────────────────────────┘
│
▼
┌────────────────────┐ ┌─────────────────────────────────┐
│ Trends over time? │────▶│ LINE CHART │
│ (see patterns) │ │ Show progression, spot changes │
└────────────────────┘ └─────────────────────────────────┘
│
▼
┌────────────────────┐ ┌─────────────────────────────────┐
│ Compare values? │────▶│ COLUMN / BAR CHART │
│ (which is bigger) │ │ Side-by-side comparison │
└────────────────────┘ └─────────────────────────────────┘
Table Views: The Detail View
Best for: Transaction lists, line-item details, export-ready data.
Case: Open Invoice List for Collections
Goal: Collections team needs a list of unpaid invoices to call customers.
RESULT
─────────────────────────────────────────────────────────────────
┌────────────────────────────────────────────────────────────────┐
│ OPEN INVOICES [Export CSV] │
├────────┬──────────────┬──────────┬─────────┬─────────┬────────┤
│ Doc # │ Customer │ Amount │ Due Date│ Days │ Status │
│ │ │ │ │ Overdue │ │
├────────┼──────────────┼──────────┼─────────┼─────────┼────────┤
│ INV-001│ Acme Corp │ $15,420 │ Dec 01 │ 23 │ 🔴 │
│ INV-002│ TechStart │ $8,750 │ Dec 10 │ 14 │ 🟡 │
│ INV-003│ GlobalInc │ $45,000 │ Dec 15 │ 9 │ 🟡 │
│ INV-004│ LocalBiz │ $3,200 │ Dec 20 │ 4 │ 🟢 │
└────────┴──────────────┴──────────┴─────────┴─────────┴────────┘
Conditional Formatting:
🔴 = Days Overdue > 21
🟡 = Days Overdue > 7
🟢 = Days Overdue <= 7
How to build:
| Step | Action |
|---|---|
| 1 | Click Add (+) → Table |
| 2 | Select your invoice dataset |
| 3 | Drag fields: Doc Number, Customer, Amount, Due Date |
| 4 | Add formula field: CURRENT_DATE - {duedate} for Days Overdue |
| 5 | Apply conditional formatting on Days Overdue column |
Conditional Formatting Setup
CONDITIONAL FORMATTING
─────────────────────────────────────────────────────────────────
Click column header → Conditional Formatting → Manage
Rule 1: If Days Overdue > 21 → Red background
Rule 2: If Days Overdue > 7 → Yellow background
Rule 3: If Days Overdue <= 7 → Green background
Rules are evaluated top to bottom. First match wins.
Sorting & Filtering
QUICK ACTIONS
─────────────────────────────────────────────────────────────────
Sort:
• Click column header → Ascending/Descending
• Hold Shift + click for multi-column sort
Filter:
• Click filter icon in header → Select values
• Filters only affect THIS table view (not other visualizations)
Export:
• Click Export → CSV
• Great for sharing with external teams
Pivot Tables: The Summary View
Best for: KPI summaries, grouped totals, executive dashboards.
Case: Revenue by Region and Quarter
Goal: CFO wants quarterly revenue breakdown by region for board meeting.
RESULT
─────────────────────────────────────────────────────────────────
┌────────────────────────────────────────────────────────────────┐
│ REVENUE BY REGION │
├───────────────┬──────────┬──────────┬──────────┬──────────────┤
│ Region │ Q1 │ Q2 │ Q3 │ TOTAL │
├───────────────┼──────────┼──────────┼──────────┼──────────────┤
│ North America │ $2.4M │ $2.8M │ $3.1M │ $8.3M │
│ Europe │ $1.1M │ $1.3M │ $1.5M │ $3.9M │
│ Asia Pacific │ $890K │ $950K │ $1.2M │ $3.0M │
├───────────────┼──────────┼──────────┼──────────┼──────────────┤
│ GRAND TOTAL │ $4.4M │ $5.0M │ $5.8M │ $15.2M │
└───────────────┴──────────┴──────────┴──────────┴──────────────┘
How to build:
PIVOT LAYOUT PANEL
─────────────────────────────────────────────────────────────────
┌─────────────────────────────────────────────────────────────┐
│ LAYOUT │
├─────────────────────────────────────────────────────────────┤
│ │
│ ROWS: [ Region ▼ ] ← Drag from Dataset Panel │
│ │
│ COLUMNS: [ Quarter ▼ ] ← Creates column headers │
│ │
│ MEASURES: [ SUM(Amount) ▼ ] ← The numbers to show │
│ [ + Add Measure ] │
│ │
└─────────────────────────────────────────────────────────────┘
Pro tip: Add multiple measures for comparison
Example: SUM(Amount), COUNT(Transactions), AVG(Amount)
Case: Sales Rep Performance Matrix
Goal: Sales director needs rep performance by product category.
RESULT
─────────────────────────────────────────────────────────────────
┌────────────────────────────────────────────────────────────────┐
│ SALES BY REP & CATEGORY │
├──────────────┬──────────┬──────────┬──────────┬───────────────┤
│ Sales Rep │ Hardware │ Software │ Services │ TOTAL │
├──────────────┼──────────┼──────────┼──────────┼───────────────┤
│ Sarah Chen │ $180K │ $320K │ $95K │ $595K ⭐ │
│ Mike Johnson │ $220K │ $150K │ $120K │ $490K │
│ Lisa Park │ $95K │ $280K │ $85K │ $460K │
│ Tom Wilson │ $150K │ $110K │ $60K │ $320K ⚠️ │
├──────────────┼──────────┼──────────┼──────────┼───────────────┤
│ TOTAL │ $645K │ $860K │ $360K │ $1.87M │
└──────────────┴──────────┴──────────┴──────────┴───────────────┘
Conditional formatting on TOTAL:
⭐ Top performer (> $500K)
⚠️ Needs attention (< $350K)
Layout:
- Rows: Sales Rep
- Columns: Item Category
- Measures: SUM(Amount)
Calculated Measures in Pivots
Create calculations directly in the pivot (not saved to dataset):
CALCULATED MEASURE EXAMPLES
─────────────────────────────────────────────────────────────────
Average Deal Size:
SUM(Amount) / COUNT(Transactions)
Win Rate:
COUNT(Won Deals) / COUNT(All Deals) * 100
Margin %:
(SUM(Revenue) - SUM(Cost)) / SUM(Revenue) * 100
Charts: The Visual View
Best for: Trends, comparisons, executive presentations.
Chart Types at a Glance
| Chart | Best For | Example Use |
|---|---|---|
| Column | Comparing categories | Revenue by product |
| Stacked Column | Part-to-whole comparison | Revenue by product by region |
| Bar | Horizontal comparison | Top 10 customers |
| Line | Trends over time | Monthly revenue trend |
| Area | Volume over time | Cumulative sales |
Case: Monthly Revenue Trend
Goal: Show revenue growth trajectory for investor presentation.
RESULT
─────────────────────────────────────────────────────────────────
MONTHLY REVENUE TREND
$500K ┤ ╭──●
│ ╭────╯
$400K ┤ ╭────╯
│ ╭───╯
$300K ┤ ╭────╯
│ ╭────╯
$200K ┤ ╭───╯
│ ╭───╯
$100K ┤───╯
│
0 ┼────────────────────────────────────────────────
Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Layout:
- X-Axis: Month
- Measures: SUM(Amount)
Case: Revenue by Product Category (Column Chart)
Goal: Compare product performance this quarter.
RESULT
─────────────────────────────────────────────────────────────────
REVENUE BY CATEGORY - Q3 2024
▓▓▓▓▓▓
▓▓▓▓▓▓
$300K ▓▓▓▓▓▓ ▓▓▓▓▓▓
▓▓▓▓▓▓ ▓▓▓▓▓▓
$200K ▓▓▓▓▓▓ ▓▓▓▓▓▓ ▓▓▓▓▓▓
▓▓▓▓▓▓ ▓▓▓▓▓▓ ▓▓▓▓▓▓
$100K ▓▓▓▓▓▓ ▓▓▓▓▓▓ ▓▓▓▓▓▓ ▓▓▓▓▓▓
▓▓▓▓▓▓ ▓▓▓▓▓▓ ▓▓▓▓▓▓ ▓▓▓▓▓▓
─────────────────────────────────────
Software Hardware Services Other
Layout:
- X-Axis: Category
- Measures: SUM(Amount)
Case: Top 10 Customers (Bar Chart)
Goal: Identify highest-value customers for account management focus.
RESULT
─────────────────────────────────────────────────────────────────
TOP 10 CUSTOMERS BY REVENUE
Acme Corp ████████████████████████████████ $340K
GlobalTech ██████████████████████████████ $310K
MegaInc ████████████████████████████ $280K
TechStart ██████████████████████████ $260K
LocalBiz ████████████████████████ $240K
FastGrow ██████████████████████ $220K
SmartCo ████████████████████ $200K
InnovateLtd ██████████████████ $180K
FutureTech ████████████████ $160K
NextGen ██████████████ $140K
Pro tip: Use the Top 10 filter in chart settings to automatically limit results.
Case: Revenue by Region & Category (Stacked Column)
Goal: Show product mix across regions.
RESULT
─────────────────────────────────────────────────────────────────
REVENUE BY REGION (Stacked by Category)
┌───────┐
$500K │▓▓▓▓▓▓▓│
│▓▓▓▓▓▓▓│ ┌───────┐
│░░░░░░░│ │▓▓▓▓▓▓▓│
$300K │░░░░░░░│ │▓▓▓▓▓▓▓│ ┌───────┐
│▒▒▒▒▒▒▒│ │░░░░░░░│ │▓▓▓▓▓▓▓│
│▒▒▒▒▒▒▒│ │░░░░░░░│ │░░░░░░░│
$100K │▒▒▒▒▒▒▒│ │▒▒▒▒▒▒▒│ │▒▒▒▒▒▒▒│
└───────┘ └───────┘ └───────┘
Americas EMEA APAC
Legend: ▓ Software ░ Hardware ▒ Services
Layout:
- X-Axis: Region
- Series: Category (creates the stacking)
- Measures: SUM(Amount)
Chart Filtering
Top/Bottom Filters
CHART FILTERS
─────────────────────────────────────────────────────────────────
Click filter icon on X-axis field:
┌─────────────────────────────────────────────────────────────┐
│ Filter Options │
├─────────────────────────────────────────────────────────────┤
│ ○ Top 10 ← Shows highest 10 by measure │
│ ○ Bottom 10 ← Shows lowest 10 by measure │
│ ○ Custom ← Define your own threshold │
│ │
│ Sort by: [ SUM(Amount) ▼ ] │
└─────────────────────────────────────────────────────────────┘
Customization Options
| Option | Where | What It Does |
|---|---|---|
| Title | Settings | Add chart title and subtitle |
| Axis Labels | Settings | Rename X and Y axes |
| Legend | Settings | Position: Top, Bottom, Left, Right |
| Export | Menu | Save as SVG for presentations |
Dashboard Portlets
Put your visualizations where users will see them.
Adding to Dashboard
DASHBOARD SETUP
─────────────────────────────────────────────────────────────────
1. Go to Home Dashboard
2. Click "Personalize" (or "Customize Dashboard" for role)
3. Add Portlet → Analytics
4. Click "Set Up" in the new portlet
5. Select: Workbook → Visualization → Done
┌─────────────────────────────────────────────────────────────┐
│ SET UP ANALYTICS PORTLET │
├─────────────────────────────────────────────────────────────┤
│ │
│ Workbook: [ Sales Dashboard ▼ ] │
│ Visualization: [ Revenue by Region ▼ ] │
│ │
│ Portlet Size: [ Medium ▼ ] (Short/Medium/Tall) │
│ Custom Title: [ Q4 Revenue Overview ] │
│ │
│ [Save] │
│ │
└─────────────────────────────────────────────────────────────┘
Dashboard Layout Example
EXECUTIVE DASHBOARD
─────────────────────────────────────────────────────────────────
┌───────────────────────────────────────────────────────────────┐
│ │
│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐│
│ │ Revenue YTD │ │ Orders This Mo │ │ AR Balance ││
│ │ │ │ │ │ ││
│ │ $12.4M │ │ 847 │ │ $2.1M ││
│ │ ↑ 12% │ │ ↑ 8% │ │ ↓ 5% ││
│ └─────────────────┘ └─────────────────┘ └─────────────────┘│
│ │
│ ┌─────────────────────────────┐ ┌───────────────────────────┐│
│ │ MONTHLY TREND │ │ TOP CUSTOMERS ││
│ │ ╱────── │ │ Acme Corp ████ $340K ││
│ │ ╱ │ │ GlobalTech ███ $280K ││
│ │ ╱ │ │ MegaInc ██ $195K ││
│ │ Jan Feb Mar Apr May Jun │ │ ││
│ └─────────────────────────────┘ └───────────────────────────┘│
│ │
│ ┌─────────────────────────────────────────────────────────────┐│
│ │ REVENUE BY REGION & PRODUCT ││
│ │ ││
│ │ ▓▓▓▓ ▓▓▓ ▓▓ ││
│ │ ▓▓▓▓ ▓▓▓ ▓▓ ││
│ │ Americas EMEA APAC ││
│ └─────────────────────────────────────────────────────────────┘│
│ │
└───────────────────────────────────────────────────────────────┘
Portlet limit: Maximum 10 Analytics portlets per dashboard
Linking Multiple Datasets
Compare data from different sources in one chart.
Case: Actual vs Budget Comparison
Goal: Finance wants to compare actual revenue against budget.
LINKED DATASETS APPROACH
─────────────────────────────────────────────────────────────────
Dataset 1: "Actual Revenue"
├── Source: Sales (Invoiced)
├── Fields: Month, Amount
└── Filter: This Fiscal Year
Dataset 2: "Budget"
├── Source: Budget Record (custom)
├── Fields: Month, Budgeted Amount
└── Filter: This Fiscal Year
Link them in one workbook:
┌─────────────────────────────────────────────────────────────┐
│ │
│ ACTUAL VS BUDGET │
│ │
│ $500K ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ Budget │
│ ● │
│ $400K ● │
│ ● │
│ $300K ● Actual │
│ │
│ Jan Feb Mar Apr │
│ │
│ Variance: Budget $1.8M | Actual $1.5M | Gap: -$300K │
│ │
└─────────────────────────────────────────────────────────────┘
How to link:
- Open workbook
- Click Connect Dataset
- Select second dataset
- In pivot/chart, drag measures from both datasets
Quick Reference
When to Use Each
| Visualization | Use When | Don't Use When |
|---|---|---|
| Table | Need every row | Showing 10K+ rows |
| Pivot | Summarizing by groups | Need individual records |
| Column Chart | Comparing categories | More than 10 categories |
| Bar Chart | Long category names | Time-based data |
| Line Chart | Showing trends | Unrelated data points |
| Stacked | Part-to-whole | More than 5 series |
Keyboard Shortcuts
| Action | How |
|---|---|
| Multi-sort | Shift + Click headers |
| Select all | Ctrl/Cmd + A in table |
| Copy data | Ctrl/Cmd + C |
What's Next
| Goal | Go To |
|---|---|
| Create calculated fields | Formula Fields → |
| Optimize performance | Best Practices → |