Multiple selection for a cube parameter (DateTime)

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Multiple selection for a cube parameter (DateTime)

    Let's say I have the following DATAC function with a linked DateTime dimension for the 4th parameter (2012 in this example).
    =PALO.DATAC("Jedox/SME","Cube_Turnover","Countries_All","2012","Versions_All","Value EUR")

    How should I use this function if I want to do the following:
    1.) extract summarized data for years 2011 and 2012? Is there a "cleaner" solution, than the following?
    =PALO.DATAC("Jedox/SME","Cube_Turnover","Countries_All","2011","Versions_All","Value EUR") + PALO.DATAC("Jedox/SME","Cube_Turnover","Countries_All","2012","Versions_All","Value EUR")

    2.) extract summarized data for last 6 months?

    3.) extract summarized data from beginning to last day of previous month (exclude current month)?
    I know I could use something similar to my example under 1.)
    = PALO.DATAC(..."DateTime_All"...) - PALO.DATAC(..."201311"...)
    but there must be some other better solution.

    Thanks in advance
  • Consolidated members in dimension hierarchy have the best performance and next is the Offsets.. Sometimes for its better to do the monkey way and have many formulae than to have a conditional if-else-if .

    I have provided 2 sets of example - one using datetime dimension and other assuming Year and Month as different dimension.


    Since you have single dimension for Year and Month i.e 201301,201302 etc members..
    its simpler to use offsets, just ensure that the element order is preserved or in a pattern..
    ie.
    201210
    201211
    201212
    201301
    201302

    in this case if I offset to 1 position left of 201301, I get 201212

    Have additional members in the DateTime dimension
    Case1:
    for 2 year consolidation you can Have a member in date time dimension like 2012_2011 which is sum of 2011 and 2012
    Case2:
    last 6 months
    Have a member in DataType dimension ( if you don't want to create a new dimension, squeeze it in Version dimension)
    ['DataType': Last 6 Months] = ['DataType': Actual','DateTime': offset(-1)] + ['DataType': Actual','DateTime': offset(-2)] + ['DataType': Actual','DateTime': offset(-3)] ......

    Case3:
    Have YTD consolidation members like 201303 YTD which consolidates 201301 & 201302.



    Have a data type(Scenario) dimension and have members like
    Last 2 Years Actual
    ['DataType': 'Last 2 Years Actual'] = [DataType:'Actual'] + ['DataType:'Actual','Years':offset(-1)]

    Last 6 Months Actual
    ['DataType':'Last 6 Months Actual','Months':'Jan'] = ['DataType':'Actual','Years':offset(-1),'Months':'Aug'] + ['DataType':'Actual','Years':offset(-1),'Months':'Sep'] + ['DataType':'Actual','Years':offset(-1),'Months':'Oct'] + ['DataType':'Actual','Years':offset(-1),'Months':'Nov'] + ['DataType':'Actual','Years':offset(-1),'Months':'Dec'] + ['DataType':'Actual','Months':'Jan']

    ['DataType':'Last 6 Months Actual','Months':'Feb'] = ['DataType':'Actual','Years':offset(-1),'Months':'Sep'] + ['DataType':'Actual','Years':offset(-1),'Months':'Oct'] + ['DataType':'Actual','Years':offset(-1),'Months':'Nov'] + ['DataType':'Actual','Years':offset(-1),'Months':'Dec'] +
    ['DataType':'Actual','Months':'Jan'] + ['DataType':'Actual','Months':'Feb']

    .... so on till december

    For 3rd case Have YTD consolidation members for Months dimension Feb YTD as consolidation of Jan + Feb , see the demo cube for this example.

    Hope it helps!

    Noel.