1

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]

enter image description here

...

enter image description here

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.

Bertrand Miot
  • 929
  • 5
  • 12

1 Answers1

0

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)

ic3
  • 7,917
  • 14
  • 67
  • 115
  • I just tried your solution and get an error : la classe de la méthode 'J!'!'org.joda.time.Months.monthsBetween est inconnue : 'org.joda.time.Months' – Bertrand Miot Mar 06 '18 at 17:58
  • By the way, maybe my question was already too oriented, because what I want is to calculate "full-time equivalent" month per month on a hierarchy containing different types of personnels and do the same on a range of months, or years, which becomes an average, maybe I don't need to know the number of months, and there is a simpler solution... – Bertrand Miot Mar 06 '18 at 18:05
  • Regarding the error have you checked that JAVA support has been enabled : https://www.iccube.com/support/documentation/mdx_integration/java_integration.php – Marc Polizzi Mar 07 '18 at 06:30
  • in the .xml, I have : java.class.path org.joda.time org.joda.time.Months Still getting the same error – Bertrand Miot Mar 07 '18 at 11:00
  • Can you send use the call stack and be sure the server was restarted. What icCube version are you using (ideally joda-timeXXXX.jar file name) – ic3 Mar 07 '18 at 12:56