Get Time Back on Your Side with Dynamic Time Filters
Meet Joshua. It’s September 29th, 2020 and Joshua 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 second Friday of the month.
What if Joshua had a way to create this report just once and be completely done with it?
Gone are the days of outdated reports and duplicated work. With SAP Analytics Cloud, Joshua can simply set the dynamic time filter, eliminating this bi-weekly strenuous task. Read on to find out how Joshua can impress his manager.
Without further ado, let’s get building! Start by creating a “Gross Margin” bar chart by “Order date”.
To do so, insert a vertical bar chart with “Gross Margin” as the measure and “Order Date” as the dimension.
Since Joshua is only interested in the current year, we want to filter the data down to 2020 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 Joshua 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.
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 2020 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 2020.
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.
Now we’ve automatically filtered down all the data into the story to focus on data in 2020. 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!
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.”
Since Joshua is only responsible for the NA and EMEA regions, let’s apply a filter on region.
Now we can set up a Dynamic Time Offset filter!
First, let’s change the hierarchy, this will improve the format so it’s easier understand, simplifying the chart to Year, Month and Day.
Joshua 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 September, the months being looked at are September, October and November.
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 September, we have now changed the date range to June, July, and August.
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 September data if the month hasn’t finished yet.