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 :)
Hello,
the 6th first month are always the same, aren't they ? Why don't you directly drop Jan, Feb, Mar, Apr, May, Jun ?
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 ?
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.
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 ".
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