DataBrewery
How to do expression in cubes along dimension, namely, Year over year calculations?
Can you use Cubes to calculate between members of date slices? For instance, I want to query, what is the year over year change in sales totals per month? Cubes can create slices along dimensions, including time dimensions. i see in the code that it knows how to calculate time slices with date expressions, e.g., 1 year ago. Can it use data from different slices in a formula and display the results? The backend SQL databases can do this, but I was hoping that Cubes would understand and do the calculation. MDX has a way of doing this, can Cubes do it?
Apparently MDX implements this as a ParallelPeriod() function. StackOverflow example at http://stackoverflow.com/questions/9895611/how-to-create-a-period-over-period-measurement-in-mdx A relevant measure for my project is "Total Sales Amount for Month." For instance, total sales for February 2015. You would have to find the "Same Period in Prior Year" for the same data. In this example, total sales for February 2014. Year over year percent growth would be (Total Sales February 2015 - Total Sales February 2014)/Total Sales February 2014. I see the MDX answer as presenting ParallelPeriod as another time series in the same dimension, but offset by one time period. In this way, the data for the parallel period can be picked up by referring to the data in the function. I am not familiar enough with Cubes package to specify what a query would look like. it would have to be like an Expression you have been working on, and be able to use the data in a parallel period in the Expression. I have seen the discussion about Cubes doing Period to Date, and possibly Year over Year involves a similar complexity.