none
Can´t get the time Intelligence formula right RRS feed

  • Question

  • Hello,

    I want to create a time filter with the years 2006, 2007 and 2008. I also want this filter to be expandable, that is, I can click 2008 for example to get the 12 months of that year to choose from (I use a multi select tree because I want to be able to pick for example January and August). This filter is connected to a scorecard, where the KPIs all come from the same data source. In the data source, the reference member is set as December 2008, the hierarchy level Month and the reference data 20081231. The member level Year and Month have the correspondingly Year and Month set in Time aggregation.

    I manage to create the filter for the years by using Year-1, Year-2 and Year-3 on three rows, but when I try to include the months I don´t get it the way I want. I have tried a whole lot of different formulas. For example, Year-1.FirstMonth gives me an error message: "Time Intelligence formula function operator '.' at 6 to 6 is invalid. Year-1.Month-6 doesn´t give me an error message, but when looking in the time intelligence preview, that formula corresponds to a blank cell, i.e. no member. Same thing with Year-1.Month-6:Month+5. The only thing that I get to work is when I use the simple formula Month-18. I then do get the value for January of 2008. It doesn´t feel right though because what happens next month, then Month-18 should become February of 2008, right? Also, I don´t get the expandable tree the way I do it now.

    So, basically, I have two questions:
     - What formula should be used to get the value of January 2008 (for example), without the risk of the "shift" to February  2008?
     - How do I create an expandable tree the way I want it?

    Wednesday, July 1, 2009 1:34 PM

Answers

  • Ah, it's those small things! I thought I had tried it all but obviously not (Year-1).FirstMonth. I followed the formula examples in DD and missed the parenthesis. With that it worked and then I used the captions to get everything the way I wanted to. Thanks!
    • Marked as answer by Marcus84 Thursday, July 2, 2009 2:40 PM
    Thursday, July 2, 2009 2:39 PM

All replies

  • I don't believe you will be able to do this with the built-in TI filters.  You could setup a named set in your cube to come up with this view that you are looking for.  I pieced together a quick example going against the Adventure Works DW sample database that gets this tree view, not sure what order you are looking to return it in, but here is an example (this hiearchy also contains Semester and Quarter information):

    with set [calendar tree] as 
    Descendants(
    {STRTOMEMBER('[Date].[Calendar].&[' + cstr(year(vba!now())- 5) + ']'),
    STRTOMEMBER('[Date].[Calendar].&[' + cstr(year(vba!now())- 6) + ']'),
    STRTOMEMBER('[Date].[Calendar].&[' + cstr(year(vba!now())- 7) + ']')}, 
    [Date].[Calendar].[Month], self_and_before)
    
    
    select {} on 0,
    [calendar tree] on 1
    from [adventure works]

    You would have to modify this accordingly based on how your date dimension and hierarchy is configured in your SSAS database and add this named set in your cube to reference.

    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Edited by Dan English Wednesday, July 1, 2009 5:31 PM typo modification
    Wednesday, July 1, 2009 4:53 PM
  • Thanks again for your help Dan. At the moment, I think trying to solve it this way is a bit beyond my thesis scope (I´m actually studying management accounting too, so the data skills are still a bit limited), but at least now I know there is no built-in way to do it, and perhaps I can get back to your solution in the future. What about the formula to get the right month (although not in a tree view), should not anyone of my formulas above work?

    Thanks
    Thursday, July 2, 2009 8:27 AM
  • You could use the same MDX I listed in an MDX query for the filter to generate the values also, so this doesn't necessarily need to be setup in the calculations tab in the cube, but if it was in the cube then you could reference this from multiple items.

    As far as your formulas you might need to tweak them a bit, but you should be able to do (Year-1).FirstMonth, that should work for you.  I would just verify that you have all of the levels defined in your Time tab in the datasource to make sure that you have the time periods mapped properly.  I don't believe you can combine the two like you are showing doing Year-1.Month-6:Month.  In this case you would just want to do Month-18:Month.

    You can reference the online documentation for the syntax that can be used - About syntax for Time Intelligence expressions.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Thursday, July 2, 2009 11:31 AM
  • Ah, it's those small things! I thought I had tried it all but obviously not (Year-1).FirstMonth. I followed the formula examples in DD and missed the parenthesis. With that it worked and then I used the captions to get everything the way I wanted to. Thanks!
    • Marked as answer by Marcus84 Thursday, July 2, 2009 2:40 PM
    Thursday, July 2, 2009 2:39 PM