Meet Mike. It's October 10th 2019 and Mike is a Sales Analyst reporting sales and gross margins to his manager in a monthly excel sheet. It’s been 10 years now and he’s been doing this repetitive task, every 2nd Friday of the month.
What if Mike had a way to create this report once and be completely done with it?
Gone are the days of outdated reports and duplicated work. With SAP Analytics Cloud, Mike can simply set the dynamic time filter, eliminating this bi-weekly strenuous task. Let’s find out how Mike can impress his manager!
Let's get building! Let's create a "Gross Margin" bar chart by "Order date".
First things first, insert a vertical bar chart with "Gross Margin" as the measure and "Order Date" as the dimension.
Figure 1: Creating a Gross Margin by Order Date Chart
Since Mike is only interested in the current year, we want to filter the data down to 2019 only. We'll be going over two possible ways to do this. In SAP Analytics Cloud, you can filter data at the widget level and on the story level. (You can also create a page filter but we won't be going over that in this blog)
In this screenshot, you can see Mike apply a widget filter and alter the hierarchy to see our gross margin by month. Widget filters are useful when you only want to see granular data on one specific chart.
Figure 2: Drill down to month level
Alternatively, we can simplify our dashboard and eliminate a few unnecessary steps using a story filter in SAP Analytics Cloud. Since we're creating a dashboard focused on 2019 only, we’re going to create a Year-to-Date filter in the story with our new Dynamic Time filter workflow. This will automatically filter down all the data in the story to 2019.
To create this filter in the story, add a Time Filter and filter on “Current” to “Date” and this will show you all the data from the past to the current date.
Figure 3: Filter Current Year to Date
Now we’ve automatically filtered down all the data into the story to focus on data in 2019. The best part? No complex formulas required! In SAP Analytics Cloud, filters operate in a hierarchical format, the story filter has priority over the page filter followed by the widget filter.
Alternatively, you can choose to manually edit within the “Set Date Range for Order Date” dialog box by clicking on the “Order Date” button under “Filters”. This view is great because it gives you a visual representation of the timeline that you are setting your Dynamic Time filter on!
Figure 4: Filter Order Date to Current year
With this default setting, the dashboard will automatically update as you move through the year the filter for year to date remains active and thus no additional upkeep is necessary.
Next, let’s do a Dynamic Time Offset. To do this, let’s make a new line chart for gross margin by region.
Add a new line chart, select "Sales Revenue" as the Left Y Axis, "Order Date" as the dimension and for Color choose "Region."
Figure 5: Gross Margin by Region Line Graph
Since Mike is only responsible for the NA and EMEA regions, let’s apply a filter on region.
Figure 6: Filter by Region
Mike only governs EMEA and NA, let’s set the filter to include the regions that are relevant for Mike. To do this, simply select the "EMEA North", "EMEA South" and "NA" dimensions.
Figure 7: Filter down to EMEA North, EMEA South and NA
Now we can set up a Dynamic Time Offset filter!
Figure 8: Filter Order Date
First let’s change the hierarchy, this will improve the format so it’s easier understand, simplifying the chart to Year, Month and Day.
Figure 9: Set Hierarchy to Year, Month and Day
Mike wants to see the last three full months of his sales revenue. To do this, we can set up a Dynamic Time Offset. In the dialog box, select Offset in the range type, alter the granularity to month and set look back to 2 months. When you set Offset and set look back to 2, you are looking at the current month plus the last two months. In this example, if we are in October, the months being looked at are August, September and October.
Figure 10: Set Offset Granularity to Month and Lookback to 2
After that, set the offset direction to look back, change the granularity to month and set the offset amount to 1. When you set Look back as 1, you are essentially shifting your time range over 1 month from the current date. Since we are in October, we have now changed the date range to July, August, and September.
Figure 11: Set Offset direction to look back one month
Voilà! It’s that easy. Now your chart will automatically display the last full three months and exclude the current month to date. This is great because you won’t be confused by partial October data if the month hasn’t finished yet.
Figure 12: Sales Revenue per Order Month filtered on NA, EMEA South and EMEA North
No longer will Mike’s manager need to check his inbox to see his sales and gross margin reports. Instead, he can simply enter his Sales report story and drill down on the Sales information for any given month, quarter, or year!
Now, the real challenge is convincing Mike’s manager that adopting one story with the one source of truth and simple-to-use filters is easier than digging through last year’s e-mail to find the specific sales report in his thousands of e-mails 😉.
P.S If you’ve already taken advantage of our Dynamic Time filters before QRC 4, we’ve recently given you the ability to improve the performance of your chart and table time range filters by enabling restricting drilling.
The Dynamic Time range that applies to widget filters will now stay on the desired granularity even if the entire time range does not have data. For more details I encourage to check out our What’s New Blog to learn more about SAP Analytic Cloud’s latest feature updates!