locked
Retrieve Current Quarter in MDX formula for Dashboard Filter RRS feed

  • Question

  • I want to show Fiscal Quarter till date in a Dashboard Filter in Desc order

    Order(LastPeriods(48,[Date].[Fiscal].[Quarter].[Q1 2011])
    , RANK([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Quarter].AllMembers)
    ,BDESC)

    What the above query returns is the last 48 Quarters till [Q1 2011]

    I want to make it generic i.e. instead of [Q1 2011] I want to specify Current Quarter in the formula...

    I tried few things, but that didnt help me so far...

    Is it possible to inject Current Quarter in the above formula ?

     

    Tuesday, April 5, 2011 4:11 PM

Answers

  • How about using this MDX query:

    TOPCOUNT ( [Date].[Fiscal].[Quarter].members, 48, [Date].[Fiscal].[Quarter].CurrentMember.MemberValue)

    This would sort descending and return top 48 quarter members.

    If you have future dates then the you would need to add NONEMPTY check in the first part of this function.

    Moreover if [Date].[Fiscal] is your hierarchy then you might try referencing [Date].[Quarter] directly.

     


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by Ravi.p.rai Thursday, April 7, 2011 2:21 PM
    Thursday, April 7, 2011 7:35 AM

All replies

  • If you are using Time intelligence, try using Quarter-48:Quarter.

    http://dailyitsolutions.blogspot.com/
    Wednesday, April 6, 2011 12:47 PM
  • I was using MDX Query filter...

    using Quarter-48:Quarter wont give me the list of last 48 Quarters, what it will show is a item in the dropdown list (which spans over last 48 quarter)

    Basically my requirement is to display last 48 quarter in Desc order from the Current Quarter...

    Wednesday, April 6, 2011 2:21 PM
  • How about using this MDX query:

    TOPCOUNT ( [Date].[Fiscal].[Quarter].members, 48, [Date].[Fiscal].[Quarter].CurrentMember.MemberValue)

    This would sort descending and return top 48 quarter members.

    If you have future dates then the you would need to add NONEMPTY check in the first part of this function.

    Moreover if [Date].[Fiscal] is your hierarchy then you might try referencing [Date].[Quarter] directly.

     


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by Ravi.p.rai Thursday, April 7, 2011 2:21 PM
    Thursday, April 7, 2011 7:35 AM
  • Thanks, it did help

    I have to use Order function around the above MDX as it was returning the Quarters yearwise..

    e.g. Q4 2010, Q4 2009, Q4 2008, Q3 2010, Q3 2009, Q3 2008

     

     

    Thursday, April 7, 2011 2:23 PM