Count of a Time Dimension''s Month Member

• Timothy Blum

  SSC Rookie

  Points: 33

  More actions

  October 26, 2005 at 8:25 am

  #169329

  I have a question - one that has been having me do searches and use the BOL and even the MSDN site, but to no avail.

  Seems like this one should be very simple, but it isn't for whatever reason.

  I have a time dimension - level Year, Quarter, Month (AKA Period).  I need to show a column that lists the active number of Months selected in a column.  So if you checked 2005, it would say 12, you say quarter 1, it says three - you say Month 1 - it says 1.  I have this using the count(descendants([Fiscal Date].Members, Month)) formula - or so it seems.  When I do this - and then filter on AND show the Date dimension using a OLAP tool, for whatever reason - it decides that the total for year is 12 no matter which part of the year is selected or deselected, and because I have eight years worth of data - for the grand total it shows 96, even when just one period is selected.  Now that's just a pain, as it kinda screws with the formulas finance is writing on the data exported from the cube into Excel that uses this CountPeriods field.  Yeah - I could tell them to fix it by hand - but I wanted to see if anyone knows of a way to get just the count of the currently selected members of a time dimension at the month level?

  Anyone have a suggestion ???

  Thnx All, L2L

• Mark Tralls

  Right there with Babe

  Points: 746

  More actions

  October 27, 2005 at 4:11 am

  #600510

  Hi Timothy

  "Descendants([<Dim Name>],[<Dim Name>].[Month]).count" should do what you want.

  On the Foodmart Sales cube it would be "Descendants([Time],[Time].[Month]).count"

  

  I have added an "(All)" level to the Time Dim to demonstrate below:

  

  Hope this helps.

  Mark