Home > BI Platform, Semantic Layer > Supercharge Your Universe with Time-Series Analysis – Part 2

Supercharge Your Universe with Time-Series Analysis – Part 2

Using time is one of the most important ways to analyze information.  Each day, week, month and year is unique and can best be understood within it’s specific context.  In my last post, we showed how you can use the Semantic Layer to create time-based filters.  Today we are going to discuss Time Specific Measures.

Creating Time Specific Measures

Time Specific Measures are important because they allow us additional flexibility within WebIntelligence.  Instead of creating two queries, one for last years sales and one for this years sales, we can create one query and then use the definition within the measure objects to filter the records we need.  The following examples are for SQL Server:

YTD Sales

sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate()) and dbo.Current_Facts.sales_date<=
 getdate() then dbo.Current_Facts.store_sales else 0 end)

Previous YTD

sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate())-1 and datepart(dy,dbo.Current_Facts.sales_date) <=
 datepart(dy,getdate()) then dbo.Current_Facts.store_sales else 0 end)

Now I can run a report and place these two measures on the report and the measures values for Current YTD and Previous YTD are already filtered accordingly.  What’s great about these measures is that they can be exceedingly complex within the universe, but they are easy for users to implement.  They don’t need to worry about accidentally filtering out too many records because the filtering within the case statement is non-exclusive.

Try this one on for size, Year to Date Sales v. Previous Year to Date Sales as a percent.

YTD Sales vs Prev YTD Sales %

case when sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate())-1 and datepart(dy,dbo.Current_Facts.sales_date) <=
 datepart(dy,getdate()) then dbo.Current_Facts.store_sales else 0 end)=0 then 0
else (sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
  datepart(yyyy,getdate()) and dbo.Current_Facts.sales_date<= getdate()
then dbo.Current_Facts.store_sales else 0 end)/sum(case when
 datepart(yyyy,dbo.Current_Facts.sales_date)=datepart(yyyy,getdate())-1 and
 datepart(dy,dbo.Current_Facts.sales_date)<=datepart(dy,getdate())
then dbo.Current_Facts.store_sales else 0 end))-1 end

Now that’s some complicated SQL, and yet, because of the power of the semantic layer, all the end user consumer has to do is to drag the object onto the report.  The database will do the rest.

You can do the same on a monthly level.  Here is a list of time-based measures that I use all the time.

Time-based Measures

As you can imagine, it’s a great way to provide options to your users that they would never have dreamed possible before.  In my final post, part 3, I will provide a copy of the universe for you to download.

«Good BI»

  1. Ben
    January 25th, 2011 at 13:14 | #1

    Dave, I came across another unique situation. The db is SQL Server 2008. The reporters want to be able to select a date range (event date between 12/3/2010 and 12/9/2010), but they don’t follow the “to-date” type of situation. The report will use the date range and bring back same range for the previous year. So by entering in 12/3/2010 and 12/9/2010, they would also get the date range for 12/3/2009 and 12/9/2009. Is there a way to utilize this prompted value, subtract a year from it, and utilize for filtering on a second query to bring back values from previous year date range?

    • February 3rd, 2011 at 07:39 | #2

      The main problem you have here is where do you want t put the restriction? In cases like this, I typically do the restriction in the SELECT statement using a CASE WHEN instead of the where statement. The SELECT statement gives me the flexibility to do the date subtracts using the DATE functions. If the SQL allows me to do the same DATE manipulation in the WHERE clause, then I’m golden.

  2. Jab
    July 11th, 2013 at 08:53 | #3

    I realize that in this method we are using one query. I have a specific requirement, where user has to run the report for one month (Provided a prompt with start date and end date in query panel) and report should display the YTD sales PTD sale and sale on the month for which user run the report. How can I achieve this?

    • David Taylor
      July 11th, 2013 at 16:25 | #4

      It would depend on whether or not the start date will also impact the YTD and PTD calculation. If so, then modify the YTD, PTD calculations accordingly. It would also be possible to do something via Derived tables if you’d like so that WebI would generate multiple queries with the ‘correct’ start and end dates (instead of using case statements and 1 query)

  1. No trackbacks yet.