locked
To extract 3 months+MDX query RRS feed

  • Question

  •  

    HI,

     

    I want to write a MDX Query which displays previous last 3months data only.

    If im in August i want te data of june, july n august 2 b displayed.

    Similarly if im in sept  i want it 2 display data of july, august n september.

    lets assume te data it has 2 display is te levels(a,b,c) 4m te dimesion Level(a,b,c,d,e,f,g) across te time dimesion.

     

    Help!!!!!!!!!!!!!

     

    Regards,

    Divya

    Friday, August 8, 2008 8:35 AM

Answers

  • The Time Intelligence Filter feature in Dashboard Designer also works well for leveraging time in filters, with simple syntax like 'Month:Month-3', you can link filters to Reports and Scorecards and retrieve the last three month of data: http://office.microsoft.com/en-us/help/HA102411371033.aspx

     

    By connecting the filter to Rows or Columns on a scorecard or Analytic Grid, you can display the last three month, if Time is in the background (which is filters on a scorecard), it will SUM the values for the last 3 months.

     

    HTH,
    Shannon

    Monday, August 11, 2008 8:36 PM

All replies

  • do you have an attribute on your date dimension that indicates the current period? I use a flag as an attribute property and do the following:

     

    lastperiods(3,filter([Date].[Month].member,[Date].[Month].currentmember.Properties("Current Date Flag")="y").item(0))

     

    if the flag is exposed as a query then you can use existing which may perform better:

     

    lastperiods(3,exists([Date].[Month].members,[Date].[Current Date Flag].&Yes).item(0))

     

    Friday, August 8, 2008 3:48 PM
  • The Time Intelligence Filter feature in Dashboard Designer also works well for leveraging time in filters, with simple syntax like 'Month:Month-3', you can link filters to Reports and Scorecards and retrieve the last three month of data: http://office.microsoft.com/en-us/help/HA102411371033.aspx

     

    By connecting the filter to Rows or Columns on a scorecard or Analytic Grid, you can display the last three month, if Time is in the background (which is filters on a scorecard), it will SUM the values for the last 3 months.

     

    HTH,
    Shannon

    Monday, August 11, 2008 8:36 PM
  •  

    Hi,

     

    Thank U so much.

    I dnt wnt 2 create TI. I want 2 create a measure 4 tis. As i want 2 supply only 3months data for te report.

    Ur Query(Last Period) is perfectly working wit select stmt. But nt wit create measure cn i knw y?

     

     

    Regards

    Wednesday, August 20, 2008 1:22 PM