Concept: This is one line graph showing the number of active users through time periods. The top buttons are option buttons which provide different data reflected by the visualization when clicking. The top presentation note is also changeable by the option buttons, which create multiple stages of presentation in one graph.

6

  • Stage 1 :

1

  • Stage 2 :

2

  • Stage 3 :

3

  • Stage 4 :

4

  • Stage 5 :

5

  • Stage 6 (Full) :

capture

PREPARING DATA

  • Require: Line chart; If statement; Option Buttons in Form Control
  • Prepare data: Data tab

7.PNG

  • Data elements:
    • Data table: This include the real data table of the whole period (Year, Month, Active Users) and preparation data for two series on the graph (Series 1 presents the dimmed line of the past period and Series 2 presents the darker line showing the period in each stage).
    • Control Button: This cell use to connect all the Option Buttons to this cell value (The number represents the option number: 1 – 6).
    • Comments: This is the table of all the presentation headline on top of each stage. And the cells J2 presents the actual cell link to the headline. (Beginning is the first headline when not clicking on any option yet).
    • Final Comments: This is the list of final comments in the final stage showing all the comments in each period. (only appear for option 6)

FORMULA

  • Data table: use IF statement in Series 1 and Series 2 to return the value based on the option. (Ex: Stage 3 presents 2 series, series 1 from Sept 2013 – Dec 2013 and series 2 from Dec 2013 – Mar 2014 -> We need Series 1 and 2 in each option show the number based on the period they cover otherwise return NA()). The If statement will changed for each period (because each period will show the number differently based on the option – reference from Excel file.
_ Year, Month, Active User (A - C): actual data
_ Series 1 (D): D2 = IF(OR($G$2=3,$G$2=4,$G$2=5),C3,NA())
_ Series 2 (E): E2 = IF(OR($G$2=1,$G$2=2,$G$2=6),C3,NA())
  • Control Button: This button will be set up later in Form Controls
  • Comments: Each of the comments from row 3 are preset up explanation about the data. Only cell J2 has If statement to pick up the comments based on each stage (Control button – cell G2)
_ Comments (J): J2 =IF(G2=0,J3,IF(G2=1,J4,IF(G2=2,J5,IF(G2=3,J6,IF(G2=4,J7,IF(G2=5,J8,J9))))))
  • Final Comments: show the comments and the date only for stage 6 -> IF statement when G2 = 6.
_ Final Comments (L): L2 = IF(G2=6,"Moonville..."," ") 
_ Date (M) : M2 = IF(G2=6,"Sep - Dec 2013"," ")

CREATE GRAPH

  • Insert Line Graph: Use two data Series 1 and Series 2

8.PNG

  • Colors and Edit: 
    • I use a dimmed blue color (Transparency 50%) for Series 2 And a blue color (Transparency 0%) for Series 1. 
    • Data point shows only the first and the last point of the Series 1 (deleting all other Data Labels of the months between) and show no Data labels or Series 2. This process will be setup for each options.
    • Data labels format as a circle in white color with blue border to flag out easily.
  • Set up Option Buttons in Form Controls
    • Open Develop / Insert / Form Controls – Option Button
    • Create 6 Option Buttons by copy and paste10.png
    • Right click on one button and select Format Control
    • 10.png
    • In Control / Cell Link -> Set up the Cell link to the Option Button Cell in data sheet (Data!$G$2)

11.PNG

  • Arrange the Option Button in the correct arrangement from 1 – 6.
  • In the last option (Option Button 6), we add more text box in the graph to show notes for each period (like below).

9.PNG

  • Link each Text Box and the Headline cell (B2) with correct Comments (Data!J2) and Final Comments and Date.

TIPS AND OPINIONS

  • Two series with a dimmed line and a solid line to compare the historical trend but still focus on the current period.
  • Notes and summary about each period helps viewers to summarize easier and to understand the whole pictures.
  • Interactive Option Button in a smooth flow can easily deliver the message to the viewers without an actual presentation of the report builder
  • This visualization can be easily transferred to PowerPoint with the sequence of images from different stages in each slide.

REFERENCES:

  • Knaflic, C. N. (2015). Storytelling with data: A data visualization guide for business professionals. Hoboken, NJ: Wiley.

About the Author BBnguyen

I am interested and working, researching about the new technology for business intelligence, big data, IoT and statistical analysis.

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Đăng xuất / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Đăng xuất / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Đăng xuất / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Đăng xuất / Thay đổi )

Connecting to %s