MDX : how to calculate number of months between a date and beginning of the year?

Question

Using iccube, I want to calculate the number of months between a date and the beginning of the year.

I have 2 hierarchies in my date dimension : [Date de sortie].[Année, semestre, trimestre, mois, jour] : contains Year, half-year, quarter, month and day [Date de sortie].[Année et Mois] contains Year and month

executing the following mdx returns me the good result (5 months)

with 
set myData as {[Date de sortie].[Année et Mois].[Mois].&[2017-01-01]:[Date de sortie].[Année et Mois].[Mois].&[2017-05-01]} 
member nbMonths as  myData.count
select
{
    nbMonths
} on 0,
myData on 1
from [Cube]

But, when trying to parametize this, it uses a period beginning with the first date with data instead of beginning the period with the calculated date (1st day of year).

with 
calculated member beginOfYear as dtWithDayOfYear(lookupByKey([Date de sortie].[Année, semestre, trimestre, mois, jour].[Jour], [Date de sortie].[Année et Mois].[Mois].&[2017-05-01].key),1).key
set myData as {lookupbykey([Date de sortie].[Année et Mois].[Mois], beginOfYear):[Date de sortie].[Année et Mois].[Mois].&[2017-05-01]} 
calculated member nbMonths as  myData.count
select
{
    nbMonths, beginOfYear
} on 0,
myData on 1
from [Cube]

...

As result you can see above, beginOfYear seems to be calculated correctly, but not used in the set myData. So, it returns 113 instead of 5.

ic3 ic3 · Accepted Answer · 2018-03-06T16:13:42

It might be complicated using only MDX, one option is using the Java interface in icCube

This would be stright forward by declaring a function that takes two dates and use Joda library :

Function NumberOfMonth( Value date1 ,Value date2 ) as
            abs(J!org.joda.time.Months.monthsBetween(date2, date1)->getMonths() )

The code would look like :

WITH 
  FUNCTION NumberOfMonth( Value date1 ,Value date2 ) as abs( J!org.joda.time.Months.monthsBetween(date2, date1)->getMonths() )
  MEMBER Diff as NumberOfMonth( [D. Time].[H. Time].[Day].&[2015-03-02].key, [D. Time].[H. Time].[Day].&[2015-10-02].key )
SELECT
 [Diff] ON 0
FROM [Sales]

------- 8 Mar 2018 -------

Don't forget to switch on Java module and set the classRepositoryActive to true (the latest if a bug that will be fixed in the next release)

MDX : how to calculate number of months between a date and beginning of the year?

1 Answers