README.md

Section 10 - Time Analysis Deep Dive

ℹ️Objective: Learn how to effectively analyze your time-based data, how to create time-based filters, calculations and visualisations.

Estimated Time: 20 mins

Exercise Description: You as a business analyst, need to assess company’s performance this year, compare operational and finance KPIs over time and learn how to enhance the dashboard with interactive capabilities to ensure company’s managers can further investigate the data.

Key Features:

• Create dynamic and fixed date-based story and page filters with multiple ranges
• Learn how to customise “current date” for greater flexibility when analysing time-based data
• Create dynamic calculations such as Year-to-Date (YTD), Previous Year-to-Date (PYTD) and others
• Learn how to define date-based hierarchies in SAC that best suit your needs

⚠️Disclaimer When completing exercises, some data values in the screenshots may not match what you see on your screen. This is because the dynamic time filters that were applied at the time the screenshots were taken is different from the current system date.

---

⚠️Quality Check! Does your screen look like this screenshot?

1. Click Home

ℹ️Welcome to the Time Analysis Deep Dive! We will also go into greater detail on the analytic capabilities available to story creators and how you can enhance yourdashboard to analyze trends over time

🚩For this section you will start with an existing story. Let's open the starting story from the file repository

2. Click Files

3. Click Public

4. Click TechEd 2021

5. Click Section 10.0 - Time Analysis (START)

⚠️Quality Check! Ensure this story was opened.

🚩As you do not have edit rights to this story, use Save As to create your own copy.

6. Click File

7. Click Save As

8. Save the file as "ANA260_Section 10_Your Initials" in MyFiles

9. Click OK

🚩Wait for the story to be copied

10. Click Edit

🚩You want to do the time-based analysis on the Historical Performance tab.

11. Select Historical Performance Tab

🚩Right now, there is no time-based filter on the dashboard, you want to narrow down the data to the current month to date. Let's create a story filter using the quick filter option for time.

12. Click Add Story Filter icon

13. Click Current

14. Click Order Date

15. Click Month under To Date

🚩You can see a story filter for the current month to date has been created and the entire dashboard has been filtered from start of this month until today.

Note: As this is a dynamic time filter, your data may look different based on today's date.

🚩 Currently the current month is determined based on system time. However, you want to provide your story viewers with the ability to determine what the current day is. For this you can add a custom current date input control.

16. Click Order Date Story Filter

17. Click Edit Filter

🚩While you created the month to date filter via quick options, the dynamic range filter dialog provides a lot of more options. First you want to add the custom current date input control.

18. Expand Current Date

19. Click + Create Current Date Input Control

20. Rename to Date Selector

21. Expand Granularity

22. Click Day

🚩Let's set the default current date to October 31st, 2021 as it is the last day of last month.

23. Expand Day Granularity

24. Click January

25. Click October

26. Click 31

27. Click OK

🚩Quality Check! Does your Dynamic Time Filter match the screenshot?

Note: Range 1: Current Month may not match due to dynamic time.

28. Click OK

🚩You can see the Date Selector input control was added to the story filter bar. In the time series chart, you can see that the data now show October data until October 31st.

🚩Let's change the Current Date to December 8th, 2021 and see how the dashboard updates.

29. Click Date Selector

30. Click October

31. Click Dec

32. Click 8

33. Click Outside the Date Selector to Collapse

🚩You can see, especially in the time series chart, that the dashboard has been updated accordingly.

🚩Next, you want to give your story viewers some more time filtering options. Such as Current Year to Date and Previous Year to Date. Of course, all these options shall be based on the Date Selector for the current date.

You can do this by adding additional ranges to the Current Month story filter

34. Click Order Date

35. Click Edit Filter

🚩The first range to add is beginning of the year up to today's date.

36. Click + Add a New Range

37. Click Include Range up to Current Period

🚩The next range to add is beginning of last year up to today's date last year. This can be done by applying an offset to the range filter

38. Click + Add a New Range

39. Expand Range Type

40. Click Offset

41. Enter 1 as the Offset Amount

42. Click Outside the Offset Amount

43. Enable Include Range up to Offset Period

The last range shall be the last 2 years as well as the current year

44. Click + Add a New Range

45. Enter Look Back as 2

46. Click Outside Look Back to Save Entry

🚩You're done with the set up of the 4 time ranges that the story view may choose from.

47. Click OK

🚩Let's review the ranges created. Switch from Current Month to the Current Year filter.

48. Click Current Month

49. Click the Second Member

50. Click outside to collapse the story filter

🚩You can see on the time series chart that the filter has been applied. Let's extend to view the last 2 years as well as the current year.

51. Click Order Date

52. Click the Last Member

53. Click outside to collapse the story filter

🚩Again, you can see on the time series chart that the filter has been applied.

🚩Right now, the story filter has still a lot of options such as configuring the time periods to go back to. You can hide this complexity from the story viewer. Let's do that and only expose the effected time range to the story viewers.

54. Click Order Date

55. Click Settings

56. Click Show/Hide

57. Deselect Look Back

58. Deselect Current Period

59. Deselect Look Ahead

60. Deselect Range Info Icons

🚩You can see that this looks much cleaner.

🚩Next you want to add some page input controls to investigate, which orders where ordered and received in a certain time frame. These input controls shall be in the lane with the Order Deliver Analysis table. Let's select the lane and add an input control each for Order and Receive Date

61. Click the Third Lane (Row)

62. Click More icon

63. Click Insert Input Control

64. Click Dimensions

65. Click Order Date

66. Click Filter by Range...

🚩This time the range shall be fixed rather than dynamic

67. Click Fixed

🚩You want the range of the order to be from October 1st to October 31st, 2021.

68. Expand Granularity

69. Click Day

70. Expand the Beginning Date Picker

71. Click 2019

72. Click 2021

73. Click January

74. Click Oct

75. Click 1

76. Expand the End Date Picker

77. Click December

78. Click Oct

79. Click 31

⚠️Quality Check! Does your range match the screenshot (October 1, 2021 - October 31, 2021)?

80. Click OK

🚩The input control was added to the page. Let's expand it and put it into the position.

81. Click and Drag the Input Control to Resize

⚠️Quality Check! Your dashboard should look like this.

🚩Let's create another input control to filter based on the receive dates between October 1st, 2021 and November 30th, 2021.

82. Click the Third Lane (Row)

83. Click More icon

84. Click Insert Input Control

85. Click Dimensions

86. Scroll and Click Receive Date

87. Click Filter by Range...

88. Click Fixed

89. Expand Granularity

90. Select Day

91. Expand the Beginning Date Picker

92. Click 2019

93. Click 2021

94. Click January

95. Click Oct

96. Click 1

97. Expand the End Date Picker

98. Click 2022

99. Click 2021

100. Click January

101. Click Nov

102. Click 30

⚠️Quality Check! Does your range match the screenshot (October 1, 2021 - November 30, 2021)?

103. Click OK

🚩The input control was added to the page. Let's expand it and put it into the position.

104. Reszie Receive Date as Wide as Order Date Range Filter

⚠️Quality Check! Your dashboard should look like this.

🚩You may have noticed that the entire dashboard is filtered on the Order and Receive date input controls that you just added. You only want to apply these to the Order Delivery Analysis table. You can use Linked Analysis to set the scope of the page input controls

105. Click the Order Date Range Filter

106. Click the More Action

107. Click Linked Analysis

108. Click Only Selected Widgets

109. Scroll and Click Order Delivery Analysis

110. Click Apply

🚩We want to repeat the steps for the Receive Date Range Filter Input Control.

111. Click Receive Date Range Filter

112. Click More Action icon

113. Click Linked Analysis

114. Click Only Selected Widgets

115. Scroll and Click Order Delivery Analysis

116. Click Apply

⚠️Quality Check! Your page input controls should only drive the Order Delivery Analysis table

🚩Let's find out which orders that were shipped after Oct 1st, 2021 have been received by October 8th, 2021.

117. Click the End Date Picker

118. Click November

119. Click Oct

120. Click 8

🚩Next you want to calculate the time it takes to deliver your orders. This can easily be done via calculations.

121. Click the Order Delivery Analysis Table

122. Click Designer

123. Click More Action Icon for Account

124. Click Add Calculation

125. Click Date Difference

The time to deliver the orders is the difference between Receive Date and Order Date.

126. Expand Time (A)

127. Click Receive Date

🚩You must specify the context of the calculation. As the delivery time is based on orders you must use Order ID in the dimension context. Also, in case the chart or table does not include Order ID, then an additional average aggregation will be applied to the calculation.

128. Expand Dimension Context

129. Click Order ID

130. Click Outside the Dimension Context Drop Down Menu to Collapse

131. Rename Calculation to Delivery Time

132. Click OK

🚩You can see the delivery time has been added to the table.

Note: You may need to scroll in the table to see the new calculation.

Let's format the calculation.

133. Click 1 Story Calculations

134. Click Edit Formatting

135. Deselect Use Unit of Underlying Measure

136. Set Decimal Places to 0

137. Click OK

🚩The calculation has been formatted. Let's order the measures in the table.

138. Click Designer to collapse the Designer Panel

🚩We want to reorder the measure within our table.

139. Right Click on Account to Open the Context Menu

140. Click Sort Options

141. Click Add Custom Order

🚩The desired order is: • Delivery Time • Sales Revenue • Gross Margin • Discount

142. Click and Drag Delivery Time to the top

143. Click and drag Sales Renue Below Delivery Time

144. Click and Drag Gross Margin above Discount

⚠️Quality Check! Does your custom sort match the screenshot?

145. Click OK

⚠️Quality Check! Does your table look like this?

🚩Next you want to compare the financial performance for this year to the performance from last year during the same period. You will do the analysis on the Financial Performance table. Select the table and open the Builder panel.

146. Scroll to the Bottom of the Dashboard

147. Click the Financial Performance Table

148. Click Designer to Open the Builder Panel

🚩The table uses a measure input control. You want to have the time calculations apply automatically to any measures in the table, this can be done using Cross Calculations. Add the Cross Calculation dimension to the table

149. Click + Add Measures/Dimensions

150. Click Cross Calculations

151. Click Outside the Measures/Dimensions Drop Down Menu to Collapse

🚩Let's add the first cross calculation as a restricted measure for the year to date time range using order date dimension

152. Click the More Actions Icon for Cross Calculations

153. Click Add Calculations

154. Click Restricted Measure

155. Expand Dimensions

156. Click Order Date

157. Expand Values or Input Controls

158. Expand To Date Under Current Period

159. Click Year

160. Rename the Calculation to YTD

161. Click OK

🚩You can see the calculation was added to the table. Let's create an equivalent calculation for the previous year's value.

162. Click Add Calculations

163. Click the More Action Icon for Cross Calculations

164. Click Restricted Measure

165. Expand Measures

166. Click Account Values

167. Expand Dimensions

168. Click Order Date

169. Expand Values or Input Controls

170. Expand To Date Under Previous Period

171. Click Year

172. Rename the Calculation to PYTD

173. Click OK

🚩Next you want to calculate the variance between last years data and this year’s data.

174. Click the More Actions Icon for Cross Calculations

175. Click Add Calculation

176. Click Calculated Measure

177. Type P

178. Click PYTD

179. Type - Y

180. Click YTD

181. Rename Calculation to Variance

182. Click OK

🚩Before reviewing the table. Let's make sure we have only the desired calculations selected.

183. Click Filter for Cross Calculations

184. Click PYTD

185. Click OK

⚠️Quality Check! Does your table look like this?

Note: Due to the dynamic time filter the data may vary.

🚩Next, you would like to adjust the order of the columns in the table.

186. Click the More Actions Icon for Cross Calculations

187. Click Edit Member Order...

🚩The required member order is:

YTD PYTD Variance Account Values Measure Selector

188. Click and drag YTD to top

189. Click and drag PYTD below YTD

190. Click and drag Variance below PYTD

⚠️Quality Check! Does your Edit Member Order match the screenshot?

191. Click Done

⚠️Quality Check! Does your table look like this?

🚩You want to make the variance stand out more easily indicating where there was an increase or decrease from last year. This can easily be done by turning it into a variance in-cell chart.

192. Right Click Variance to Open the Context Menu

193. Click In-Cell Chart

194. Click the In-Cell Chart

195. Expand the Comparison Tile

196. Click Variance Bar

⚠️Quality Check! Does your table look like this?

🚩Next let's look at some time navigation inside a chart. The chart shows the Sales Revenue over time. Let's drill a level down to understand how 2021 is performing.

197. Drag the Horizontal Scroll Bar to the Right

198. Left Click the 2021 Bar

199. Click the Drill Down Icon

🚩You can see the chart now show the Sales Revenue by quarter. However, you're more interested in it for the half year. Let's change the hierarchy via the builder panel.

200. Click the Drill Icon for Order Date

201. Click Set Hierarchy

202. Expand Hierarchy

203. Click Year, Half-Year, Quarter, Month, Day

204. Click Set

🚩The chart has been reset to the new hierarchy. Let's change the level via the builder panel.

205. Click the Drill Icon for Order Date

206. Click Level 3

⚠️Quality Check! Does your chart look like this?

🚩Now let's look at quickly showing the year over year changes if the Sales Revenue. This can be done via time calculation quick options if time if in the chart

207. Click the More Action Icon for Sales Revenue under Measures

208. Click Add Time Calculation

209. Click Year Over Year

210. Remove Sales Revenue

⚠️Quality Check! Does your chart look like this?

🚩You can see that H1 2021 is significantly lower than H1 2020. Let's drill into this more.

211. Click the H1 2021 Bar

212. Click Drill Down

🚩The major deviation was in Q1 of 2021.

ℹ️You have concluded the Time Analysis Section! Save your document.

🚩Press Ctrl + S to save your story

Summary

You have completed the entire Time Analysis section!

You are now able to:

• Create dynamic and fixed date-based story and page filters with multiple ranges
• Customise “current date” for greater flexibility when analysing time-based data
• Create dynamic calculations such as Year-to-Date (YTD), Previous Year-to-Date (PYTD) and others
• Define date-based hierarchies in SAC that best suit your needs