One of the common business requirement for Dashboards is to be able to see last/rolling 12 month trend on a chart. To meet this requirement, you’ll need to get a data set from the data source and if it’s a SSAS cube, How do you get last/rolling 12 months using MDX?
Note: It’s not a rolling 12 month sum. If you’re looking to do that, please refer: Sum of Last N Years, Quarters, Months, Periods.
Here’s the pseudo code, I hope it gives you a good starting point:
[code language=”sql” gator=”false”]
SELECT NON EMPTY
[Fiscal Period].[Fiscal Period]
(SELECT STRTOMEMBER(@FiscalPeriod).Lag(12):STRTOMEMBER(@FiscalPeriod) on 0 FROM [CubeName])
Note the use of sub select & query parametrization. You’ll need to pass in current month value in the fiscal period parameter. I hope this helps!
I was playing w/ a time series data set in Excel 2010 and learned how to add a Trend-line and in this blog post, I’ll share how I added it:
First up, How is Trend-line useful? Here are few answers:
– It helps us see how data is changing over time, in other words, it helps us find “trends”
– It helps us forecast future.
With that, here is the chart without Trend-line:
Now let’s add the trend-line and you’ll be able to compare on your own how Trend-line makes it easier to spot “trends”. Here are the steps:
1. select the line > right-click > add trend line
2. configure the trend-line options
3. I also changed the line style
4. And Here’s the chart w/ trend-line
In this post, we saw how to add trend-line in the time series chart in excel 2010
I was recently searching for a way to do some text mining on Twitter Data. I was interested in a tool that has some “library” that helps to fetch twitter data & later, I wanted to create visualization like say word cloud, time series. etc. Turns out that “R” perfectly suited my needs because of libraries/packages such as TwitteR and ggplot2 – And so, I downloaded and installed R and RStudio on my windows machine. Here are the steps (I am using Windows Server 2008 R2 machine 64 bit):
1. Download R for Windows:
2. After downloading it > Install it by leaving all options to default.
3. Download RStudio Desktop for windows:
4. Install RStudio > leave all options to default.
5. Open RStudio > In the Bottom Right Pane, switch to Packages Tab > Click on Install Packages > In the packages box, type in ggplot2 and > click on Install.
5. Check that ggplot2 successfully unpacked and installed > Now similarly install the package: twitteR > make sure it is successfully unpacked and installed.
6. And I quickly created a chart of Twitter UserName vs Number of Tweets for #sqlpass:
we can do much mire but just wanted to show how you can do social media analytics with R!
In this blog post, we saw a step by step process to download and install R and R studio on a windows machine.