Magento Business Intelligence Help Center

Using Time Options in the Visual Report Builder

BROUGHT TO YOU BY

Avatar

If you’ve ever tinkered in the Visual Report Builder - and we’re sure you have by now - you’re probably familiar with the global Time Range and Interval settings. These allow you to analyze the data in your report for a specific time period.

Did you know...
...That there's a Training Video for this topic? Click here to check it out!

However, for some analyses, you may need to consider different Time Ranges or Time Intervals in the same report. That’s where Time Options come in. To give you a better idea of how to use Time Options in your reports, we’ll go over a few use cases:

Let’s get started. If you want to follow along with some of the sample reports we’ll build in this article, open the Visual Report Builder before continuing.

Analyzing metrics without timestamps

Some metrics simply cannot trend over time because the data isn’t collected or stored with an associated timestamp. For example, an inventory table will often contain only one row for each SKU. In that case, you should create the metric without specifying a timestamp.

When using such a metric in your reporting, you’ll notice that adding this metric to a report automatically sets an independent Time Interval of None and Time Range of Global:

Adding a metric without a timestamp to a report.
Click for a closer look!

Giving one metric an independent Time Interval

Time Options allow you to create time-based ‘100% charts’ to identify which day, week, month, or year contributed the most value during a specific time range. In this section, we’ll create a chart that shows you the percent of revenue generated in each calendar month of a year.

This type of report can be useful if you want to compare revenue generated year-over-year. For example, if a chart for 2015 revealed that January contributed 18% of revenue for the year and one for 2016 showed only 8%, you could start researching what might have happened.

  1. Add your Revenue metric to the report.
  2. Click the Duplicate option to make a copy of the metric.
  3. Click the global Time Range option, then Moving Time Range. Set this to Last Year.
  4. Click the global Time Interval option and set it to Monthly.
  5. Report Builder automatically adds a second Y-axis for a second metric. Uncheck the Multiple Y-Axes box.
  6. Next, we’ll apply an independent Time Interval to the first metric. Click the Time Options option (clock icon) to the right of the first Revenue metric.
  7. Click Time Options in the expanded window that displays above the report.
  8. In the dropdown, set the following:
    • Time Interval - set this to None.
    • Time Range - set this to Last Year by first clicking Custom, then Moving Range and then the Last Year option.
  9. Click Apply to save the interval and range settings. This will create a metric that calculates the total revenue for the previous year. Next, we’ll use this metric as the denominator in a formula.
  10. To see the percent of revenue for each month, we need to add a formula to the report. Click the Add Formula button.
  11. Enter B/A in the formula field and select % Percent from the dropdown next to the text field. This formula divides the amount of revenue from a specific month last year by the total amount of revenue last year.
  12. Click Apply Changes.
  13. Hide both of your input metrics and rename the formula.

Now we can see just how impactful each month was last year:

Using Time Options to give a metric an independent Time Interval. 
Click for a closer look!

Comparing the same metric across different Time Ranges

Heads up!
In this example, we’re using a custom dimension called Day number of the month. If you want to create this report and don’t already have this dimension in your Data Warehouse, reach out to support for assistance.

The two most common examples in this category are (1) comparing growth metrics (revenue year-over-year, month-over-month, etc.) and (2) better understanding recent inventory or item sales trends.

To demonstrate this use case, we’ll look at the daily revenue for the previous month compared to the same month from the previous year. Let’s say we want to look at the revenue for each day of January 2016 and then compare that to January 2015, January 2014, and so on – this report would show us that.

  1. Add your Revenue metric to the report.
  2. Click the Duplicate option to make a copy of the metric.
  3. Rename the metrics. We're 2014 and 2015 :
  4. Rename the first metric to Items sold last 7 days.
  5. Rename the second metric to Items sold last 28 days.
  6. Click the global Time Range option, then Moving Time Range. Set this to Last Month.
  7. Click the global Time Interval option and set it to None.
  8. Click the Time Options button (clock icon) next to the second Revenue metric.
  9. Click Time Options in the expanded window that displays above the report.
  10. In the dropdown, set the following:
    • Time Interval - set this to None.
    • Time Range - set this to From 14 Months Ago To 13 Months Ago by first clicking Custom then Moving Range. Use the fields and dropdowns at the top of the menu to set the range. This setting allows us to see the revenue for the previous month, but in the previous year.

      Don’t worry if the metric disappears from the report - setting an independent time options automatically hides the metric from the report. To redisplay it, click the Show option next to the metric.

      Redisplaying a metric after setting independent time options. 
  11. Click Apply to save the interval and range settings.
  12. Next, we’ll add our custom Day number of the month dimension by clicking Group By and selecting the dimension. This will return the day number of the month of an order - for example, an order placed on March 2 will return a “2.”
  13. In the Group By dropdown, select Show All and click Apply. This will effectively create the X-axis values for the report:


    Applying a Group By and creating the X-axis values.
  14. Rename the metrics. In our example, the first metric is Revenue - 2015 and the second is Revenue - 2014.

Another common use of custom Time Options is to determine weeks of supply. Especially during the holiday season or a special promotional period, you may want to consider items sold over the last week, month, and previous promotional period to make informed purchasing decisions.

Remember to set the time ranges to what you need when building this report yourself. That being said, let’s dive in:

  1. Add your Items Sold metric to the report.
  2. Click the Duplicate option to make a copy of the metric.
  3. Rename the metrics. You can use the same names we are, or use something that's similar:
  4. Rename the first metric to Items sold last 7 days.
  5. Rename the second metric to Items sold last 28 days.
  6. On the Items sold last 7 days metric, click the global Time Range option then Moving Time Range. For this example, we’ll set it to Last 7 Days.
  7. Click the global Time Interval and set it to None.
  8. Next, we’ll define the Time Options for the Items sold last 28 days metric. Click the Time Options button (clock icon) to the right of the second Items sold metric.
  9. Click Time Options in the expanded window that displays above the report.
  10. In the dropdown, set the following:
    • Time Interval - set this to None.
    • Time Range - set this to From 29 days to 1 day ago by first clicking Custom, then Moving Range. Use the fields and dropdowns at the top of the menu to set the range.
  11. Click Apply to save the interval and range settings.
  12. Duplicate the Items sold last 28 days metric and open the new metric’s Time Options. Set the options to the following:
    • Time Interval - leave this as None.
    • Time Range - change this to the date range that aligns with the promotion you’re interested in by clicking Specific Date Range and then entering the appropriate dates.
  13. Rename the metric Items sold during last promotion or something similar.
  14. Add your Units on hand metric.
  15. Next, we need to add the calculations that’ll show us the weeks on hand, considering sales trends, for the time periods (last 7 days, last 28 days, and last promo period) we’re including in the report. You’ll need to do this once for each time period.

    To create the formulas, click Add Formula. Enter the formulae below, clicking Apply Changes when finished. Repeat this for each of the three time periods:
    • For the last 7 days time period, enter D / A in the formula field.
    • For the last 28 days time period, enter D / (B/4) in the formula field.

      Note: It is important to normalize your selected time ranges here. Twenty-eight days should be broken into four weeks in this example. You may need to apply different logic to the formula.
    • For the last promo period, enter D / C in the formula field.

      Applying formulae to the time periods in the report.

  16. Lastly, customize the report by hiding the metrics and adding a SKU or a similar dimension to the report as a Group By.

So, what can we take away from this example? It appears that current inventory levels were well-situated for a product-wide 14-day sale. However, adding a comparable promotional period suggests that the company needs to make some changes – either by ordering more inventory and only promoting the items with enough units in stock.

Wrapping up

Your customers behave differently over time, so you should expect to see variances in data when performing analyses. Setting custom Time Options enables you to quickly create complex analyses, enabling data-driven decisions that factor in historical trends.

Related:

Was this article helpful?
1 out of 1 found this helpful

Comments

Powered by Zendesk