YTD, MTD etc for Current Point In Time
I've set up time intelligence on my cube. This works fine. I can get to:
|
| YTD | YTD Prior Year | YTD Index |
| Jun 2006 Qtr | 502962 | 467145 | 107.6672125 |
| Sep 2006 Qtr | 733396 | 916144 | 80.05248083 |
| Dec 2006 Qtr | 733396 | 1440868 | 50.89959663 |
| Mar 2007 Qtr | 733396 | 1850595 | 39.63028107 |
with the following MDX.
SELECT { [Time Calculations].[YTD], [Time Calculations].[YTD Prior Year], [Time Calculations].[YTD Index] } ON COLUMNS ,
{ [Time].[Fiscal Hierarchy].[Fiscal Year].&[2007].CHILDREN } ON ROWS
FROM [mycube]
WHERE ( [Measures].[Order Quantity] )
This works fine if I have time series on rows and time calculations as columns. What I want to be able to do is display YTD / MTD as of now (I have an attribute which flags today's date). I'd also like YTD prior year to equal up to including the equivalent date last year. These would appear as columns and then I would have data from other dimensions displayed on rows. Something like this:
| MTD | MTD Prior Year | YTD | YTD Prior Year |
| Product 1 | 128 | 118 | 1050 | 1415 |
| Product 2 | 1 | 1 | 12 | 14 |
| Product 3 | 129 | 25 | 646 | 554 |
| Product 4 | 45 | 37 | 92 | 445 |
To get this to work do I need to amend my MDX or do I need to change the structure of my cube? Could somebody tell me the best way of achieving this? Thanks.
[11295 byte] By [
mamo] at [2007-12-25]
Thomas
I probably haven't explained what I'm trying to achieve that well. I don't think your response answers my query. In order to achieve what I need, my cube needs to understand what the current date is. I don't want to be substituting values within my mdx statement (ie year = 2007, period = 6 etc).
In AS2000 I had custom rollup expressions that I used to give me fixed ytd, mtd (as of latest cube update). I think I can probably achieve the same thing using Time Calculations (although my mdx writing skills aren't the best!). I just wondered whether there was an recommended way of structuring my cube in order to achieve this?
Mark
OK. You would like dynamic time members that points to the latest date?
This is an example of how I have done it with a normal time dimension with one calendar hierarchy.
Tail(Filter([Time].[Time_Calendar].[Month].Members,(Time.[Time_Calendar].Currentmember,[Measures].[MyMeasure])>0)) The logic here is that to use a measure like actual sales that you know will give you a point of reference in time. |
There are several other ways to solve this.
HTH
Thomas Ivarsson
I can get:
PeriodsToDate([Time].[Fiscal Hierarchy].[Fiscal Year],Filter([Time].[Fiscal Hierarchy].[Fiscal Period].members,[Time].[Fiscal Period Current].[1] ).item(0))to work as a set.
However if I create a member:
Aggregate(PeriodsToDate([Time].[Fiscal Hierarchy].[Fiscal Year],Filter([Time].[Fiscal Hierarchy].[Fiscal Period].members,[Time].[Fiscal Period Current].[1] ).item(0)))
it doesn't return anything. Is there anything I need to do to get it to apply to all or particular measures? Thanks.
Mark
In my previous example you use that MDX to build a set and call that CurrentMonth. It is as easy to change to CurrentDay.
With this set you can start building other named sets refering to this named set.
CurrentMonth.Item(0).Item(0).Lag(1) will give you the previous month with data.
I know that there is an issue with Aggregate() if you use a time function(YTD(),ParallellPeriod()) in it. It will return null. So far I have not found out if it is a bug or a feature.
Can Paul G , Chris Webb, Mosha or Deepak comment?
Regards
Thomas Ivarsson
