Dashboard Building 101

Exercise1 - Forecast Monthly Revenue Trend

Steps
Screen-shot
1. Review the Field list and click on Revenue and Admit Date
2. Click the arrow on the top right corner of the Visual and select Maximize
3. Expand the Field Wells section, use the arrow on the Admin Date field and change aggregation to Month
4. Use the menu in the upper right of the visual, click … and choose Add forecast, hit ‘apply’ on the Forecast properties menu on the left
a. Optional: when adding/editing the forecast, choose the forecast 6 periods backwards. It will show how the forecast compares to the actuals for the previous 6 months
5. Rename visual to ‘Monthly Sales Trend’
Alt text
Now click on any sample point on the chart and review the sample menu. Select Analyze Contribution to Revenue. Pick Category, Function, Hospital and Physician for the fields to analyze. Also, change the date range from 2015 to 2018. Review the contribution %age of each category. Alt text

Exercise2 - Visualize Year over Year Revenue

Steps
Screen-shot
Add a new visual by duplicating the previous visual from the visual menu. Select KPI as the Visual Type (bottom left of the screen)
In the field wells, click arrow in ‘Admin Date’ to change the aggregation level from Month to Year
Alt text
Click Revenue menu and choose more formatting options as follows
Decimal Places = 0
Units = Thousands
Alt text
Now click on format visual again (by clicking arrow menu and click on ‘settings’ icon) and select Difference as percent(%) under comparison method.
Now change the visual title to YOY Sales and make the visual size smaller.
Alt text

Exercise3- Hierarchical Drill Down Visuals

Steps
Screen-shot
1. In the upper left, choose ‘+’ and then ‘Add visual’
2. In the list of fields, select Hospital and Revenue. Select Vertical Bar Chart in the Visual Types
3. In the list of fields, drag the Category field just beneath the Hospital field in the field Wells, until you see the message Add drill down layer.
Alt text
4. Now click on a Hospital bar and you will see an option to Drill down to Category, it will show just Category data specific to that hospital. Alt text
Optional - you can add the Sub Category field beneath Category field to add a 3rd level in the hierarchy Alt text

Exercise4- Visualize % of Total Sales by Category

Steps
Screen-shot
1. Create a duplicate of the previous visual
2. In the Visual Type, select Donut chart
3. Use the arrows to drill down into the category values
4. Now use the menu on the visual and choose ‘Format visual’
5. Expand the section for Data labels, and check the box for Show metric
6. Expand the section for ‘Legend’ and uncheck Show legend on the top
Alt text

Exercise5 - Visual Actual vs Target Goal

Steps
Screen-shot
1. In the upper left, choose ‘+’ and then ‘Add visual’
2. In the Visual Type, select Gauge chart
3. In the list of fields, select Revenue
4. In the upper left, choose ‘+’ and then Add calculated field
5. In the formula, type the number 20,000,000 (Twenty million), and name it Goal
6. Add this new Goal field to your visual (can just click on it or can drag it to the Target Value field well).
7. Change the aggregation on the Goal field from Sum to Avg
Alt text
Optional:
Play around with the formatting options for the Gauge chart:

a. Change angle to 300 degrees

b. Change thickness to medium

c. Add 20% padding

Alt text

Exercise6- Review ML Insights

Steps
Screen-shot
1. Click the ‘Insights’ menu on the left. Notice all the suggested insights QuickSight has generated based on what has been built do far!
2. Scroll to the top, hover over the Month over month change insight and click the ‘+’ to add it to the dashboard.
Alt text
3. Repeat for the Forecast insight.
Resize the insights’ height and move them next to each other
Alt text
4. Use the menu on the Period over period insight and choose Customize narrative
5. Highlight the text increased by and then change the font color to Green. Change the text color of decreased by to Red
So far we added two suggested insights and slightly customized one of them. Now we will show how to add a new insight from scratch.
Alt text

Exercise7 - Add Custom Insight

Steps
Screen-shot
1. Click the ‘+’ button in the upper left and choose ‘Add insight’
2. In the computation drop down, choose Top movers, then click Select .We will add fields to this insight just like we added fields to our visuals (click on fields or drag and drop them).
3. Expand the field wells and then select the fields Admit Date, Function and Revenue
4. Use the menu on the AdmitDate field and change the aggregation to Month
5. Now we see the top 3 biggest movers (by comparison of the change in sales for the most recent two months of data)
6. Rename this from Insight to Top Functional Movers
7. Resize all the insights and align them across the top of the dashboard

Note: You can use calculated fields, functions and parameters in narratives. You can add conditional(IF) statements to enable conditional verbiage and conditional formatting. You can also edit the computations to adjust how many periods you want to forecast, etc.
Alt text

Exercise8 - Conditional Formatting

Steps
Screen-shot
Conditional formatting allows adding cell background and text colors based on a condition.

1. Click the ‘+’ button next to your tab for Sheet 1 to create a new Sheet
2. Rename Sheet 1 to ‘Summary’ and Sheet 2 to ‘Details’
3. On the Details sheet, choose the ‘Pivot Table’ visual type
4. Add the fields ‘Admit Date’, ‘Payer Segment’, ‘Hospital’, ‘Category’, ‘Revenue’, ‘Profit’ and ‘Cost’ (just click on them)
5. Add totals and subtotals from the visual menu
6. Resize the visual to fit the full width of the screen
7. Rename the visual title to ‘Details’
Alt text
In this exercise, we will conditionally format the Revenue field based on the values in the Profit field. if the Profit goes below $0, Revenue is highlighted Red and green if the value is above $0.

Go to the visual menu and select Conditional formatting. Select column Revenue and choose Add background color. Set configuration as below

1. Format field based on Profit
2. Aggregation Sum
3. Condition Less than
4. Value 0
5. Change color to red
6. Select Add Condition
7. Change condition #2 to Greater than and value to 0.
8. Change color to green and hit Apply
9. You will see Revenue cell being colored based on the applied applied.
Alt text

Exercise9 - Filtering

Steps
Screen-shot
Let’s review how filtering is used in QuickSight

1. With the previous visual selected, click on Filter menu in the left pane.
2. Click the + button on top and add Category to create a new filter.
3. Review the distict list of Category values in Filter list
option under Filter Type
4. You can also search for specific category values
in the search bar
. Select Ante/Post Partum, Critical Care and BMT
and click apply. Notice the pivot visual is filtered by the selected values .
Alt text

Exercise 10- Filtering with Parameters

Steps
Screen-shot
1. Now with the previous visual selected, let’s set up a parameter for the Category field. We will use this parameter to send a drill down from the donut chart on our Summary sheet to the table on our Details sheet.
2. Click on Parameters menu in the left pane and click on ‘+’ to add a new parameter, name it Category and then click Create
3. Choose Filter on the next screen that pops up (we are not going to make a control for this one)
4. Switch to Filter pane again, click the Category filter to expand it
5. Change the Filter type drop down to Custom filter
6. Check the box for Use parameters
7. Choose Yes on the popup that asks if you want to change the scope of this filter
8. Select the Category parameter
8. Click Apply, then Close . Now we have a filter that is driven by a parameter for this table. We can set the value of this parameter via the URL.
Alt text
9. You can try to append #p.Category=Surgical to your URL while on the Details sheet.
For example:
https://…./sheets/820fa34d-a27f-43d5-bf2e-9c159100d1bc#p.Category=Surgical
Alt text

Exercise 11- Control Filter

Steps
Screen-shot
1. Now with the previous visual selected, click on the drop down menu for Category parameter in Parameters pane.
2. Select Add Control and enter/select following values:

a. Enter Category Selector as Display name.
b. Select Single select drop down in Style drop down.
c. Select Link to a data set field
d. Select the existing dataset
e. Under Select a column, select Category
f. Click Add
Alt text
3. A category selector will appear right below the sheet header
4. Select any category value, e.g Critical Care, it will filter the pivot visual accordingly
Alt text

Exercise 12 - Same Sheet Filter

Steps
Screen-shot
Next we will see how to use a slice in a visual to filter data in other visuals in the same sheet

1. Highlight the donut chart in the Summary sheet.
2. Click on Actions in the left pane .
3.Under Quick create, click Filter same-sheet visuals.
4. Click on Critical Care and notice filtering action on all other visuals
Alt text

Exercise 13 - URL Action Filter (Optional)

Steps
Screen-shot
Next we are going to set up the drill down
from the Summary sheet.
We will use a feature called URL actions to do this.

1. Go to the Details sheet, copy the URL from your address bar
2. Go back to the Summary sheet, click the donut chart visual and click Actions in the left panel
3. Edit Action 1, and in the action type select URL action.
4. Now paste the URL from the ‘Details’ sheet and add this to the end of the URL: #p.Category=<<Category>>
5. Now when you select any category, it will take you to the Details sheet and it will be filtered to the Category you selected!.

Alt text

Exercise14 - Time Range Date Filter

Steps
Screen-shot
Let’s filter the date for a specific time-period


1. Add a new Pivot Visual with Hospital, Category and Revenue fields.
2. Next add a filter for the Admit Date field, choose Time Range as the Filter Type.
3. Pick 3/1/2015 as the start date and 3/31/2016 as the end date.
Alt text

Exercise15 - Anomaly Detection

Steps
Screen-shot
1. Click the ‘+’ button in the upper left and choose ‘Add insight’
2. In the computation drop down, choose ‘Anomaly detection’, then click ‘Select’
3. In the list of fields, select Admit Date, Category and Revenue
4. On the ‘Admit Date’ field, change the aggregation to Month
Alt text
5. Click the ‘Get started’ button inside the insight
Here you can configure the max number of anomalies to show, schedule how frequently you would like it to run, if you would like to analyze combinations of the categories/dimensions you have added (we only added one in this example) as well as what fields you would like to run ‘Contribution Analysis’ with. Contribution Analysis is very powerful – once an anomaly is found, you can explore the anomaly and see what contributed to (what drove or caused) the anomaly.
6. In the dropdown for Contribution Analysis Dimensions, choose Patient ID, Payer Segment, Physician and Service.
7. Click Save and then Run Now in the visual
Alt text
8. It will take a few minutes to run, but it will come back with some anomalies Alt text
9. Click the arrow menu on the insight, click … and choose Explore anomalies
10. On the left you can see what contributed most to those anomalies. By hovering over any of them you will see more information too. (In this sample data set we have gotten to a deep enough level that certain cities and contacts either had or did not have records in the previous month, which is why you see so many +100% and -100% changes, and contribution percentages that are over 100%).
Alt text

Exercise16 - Customize with Themes

Steps
Screen-shot
Now, let’s review the Themes features in QuickSight which can be used to change the Color scheme of the dashboard background, color gradients for data and layout options. Also, available is the option to use custom colors to match with the color scheme of your application.
1. Click Themes in the left panel and choose one of the starter themes - Midnight theme and click Apply
2. Once in the Theme Editor, review all the panels to see the extent of customization possible.
Alt text

Exercise17 - Publish the Dashboard

Steps
Screen-shot
In the end, publish the dashboard by following the steps below:

1. Click Share in the upper right corner and choose Publish Dashboard
2. Enter a dashboard name Oktank Health Dashboard
3. Additionally review the options under Advanced publish options. Notice the ability to block csv download and filtering.
Alt text