Finding 1st six months of Last year

[Shamar]

Hi Gurus,

I want to find the first six months of last year  with MDX function. Can anyone please help me to achieve this.

Lastchild([Time]) is 2015
prevMember(Lastchild([Time])) is 2014 , Then how can i get 1st six months of 2014

Environment : cognos 10.2.1
source   : Cube
DB : Sql server

Thanks in advance :)

[TheFrenchGuy]

Hello,

the 6th first month are always the same, aren't they ? Why don't you directly drop Jan, Feb, Mar, Apr, May, Jun ?

[Shamar]

Hi FrenchGuy,

Thanks for your response :)

Actually i need to find the Growth % for first six months of current year against last year.
On this case am trying to do something like this :
aggregate([measure] within set  for first six months of current year) / aggregate([measure] within set  for first six months of last year)-1

Is there any other way ?

[schrotty]

Hi,

there exist several ways to get the first 6 month of a year. Here is one of them:

Create a Data Item with your Previous Year prevMember(Lastchild([Time])) and label it "2014".
Now lets go to the Month-Level:
To get first Month of the Year use firstChild([2014]) .
Go to the 5th Member after the 1Month: lead(firstChild([2014]);5).
Get a set with the first 6 months of the year: lastPeriods(6;lead(firstChild([2014]);5))

Similar for the current year.

[Shamar]

Hi Schrotty,

Gudos & thanks , its working fine for current year.

I followed as per your steps,
When finding first six months of prior year , it says no data available , however we have years since 2010.
Do you have any clue why its saying " no data available ".

[schrotty]

Hi Shamar,

it's good to know that the current year is working fine. :)

Have you created a slicer / filter in your report for the current year? (delete it)

Please describe your Time-Hierachy. (Levels, Order of the Members). Otherwise it is not easy to find the issue.


Schrotty