Anyone know of an easy way to have Transformer handle YTD Totals? As an easy example, if I sold 10 items in January, I would see 10 in for my measure count for January. If I sell 20 more items in Febuary, I would expect to see 30. Any help is appreciated.
Thanks
Maybe I'm missing something, but is there a reason you're not using the relative time categories for your date dimension? There, you can have YTD, MTD, Prior Month etc.
Yeah, what you are talking is nice for always having the latest YTD Value (Already in the cube btw). What I am talking is actually (Envision a Crosstab here) having the history of YTD Values. So if I slap in Months as my columns and Region as rows and use a YTD Measure for Quantity Sold, I would see increasing values as I go across the months. This measure is key to calculating certain ratios.
Hmm, I THINK I know what you mean. In the crosstab scenario described by you, having YTD in the measures section would produce just one value (the YTD value) and not a series of values (for lack of a better term!) like you need.
hmmm
assuming that this can't be created at the ETL level (funny how quickly DBA's come up with objections to anything "those Cognos guys" say), I would think your options are very limited.
I think I remember creating special categories to display months at YTD level, but I don't quite remember the exact steps now :-\
More than likely, you'll have to end up manipulating RS to do this and that can have adverse performance depending upon the number of records your cube has
I wonder if you can source your cube with the YTD value for every revenue period in your Cube?
Your simplified source data might look like:
Region Month Qty YTD_Qty
'West' 2010-01 10 10
'West' 2010-02 20 30
'West' 2010-03 12 42
If you crosstab it I think you'd get what you're after and probably just need a time state rollup setting to avoid summing across periods.
I had a vaguely similar situation where a cost measure is incurred in only one period but needs to be carried through every period for the life of the item in order to perform various calcs. I'm still working on it (though got pulled to other things) and cubes are a weak area for me, so put a big, fat "FWIW" on this...
:)
Found the RS code snippet below elsewhere, so not claiming any intellectual rights over it ;D
Assuming you have time in your rows:
aggregate([Measure] within set periodsToDate([NS].[Time].[Hierarchy].[Year], currentMember([NS].[Time].[Hierarchy])))
Very good... I till try this and let you know. I appreciate the help.
I gave this is a shot in RS for grins and it does appear to work. However, I did get our ETL People to add it physically to the Database. ;) So all is well.
Lucky you -- accommodating ETL people!!
Quote from: ComplexQry on 25 Jan 2011 01:49:55 PM
I gave this is a shot in RS for grins and it does appear to work. However, I did get our ETL People to add it physically to the Database. ;) So all is well.
It worked?
Ok, then I claim credit :D