locked
Aggregating time periods in Cubemember/Cubevalue formulas RRS feed

  • Question

  • Hi

    I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.

    The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like

    =CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.

    Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.

    In other words - Is there an easier way of for say jan to july 2015 totals than

    =CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))

    I haven't tested this but have assumed it works but a bit long and clumsy.

    Thanks

    Brian


    Brian Hyam


    • Edited by Brian Hyam Sunday, August 23, 2015 1:57 PM
    Sunday, August 23, 2015 1:56 PM

Answers

  • Hi,

    you can use CUBESET instead of CUBEMEMBER.

    In CUBESET you can define range from cubemembers like this "{cubememberexpression1:cubememberexpression2}"

    =CUBEMEMBER("OLAPCUBE","{[Time].[Time].[Year].&[2015].&[1].&[1]:[Time].[Time].[Year].&[2015].&[2].&[2]}")

    But you should do these kind of calculations in your OLAP database. This is very common YTD measure.

    • Marked as answer by Charlie Liao Monday, August 24, 2015 4:51 AM
    Sunday, August 23, 2015 2:36 PM

All replies

  • Hi,

    you can use CUBESET instead of CUBEMEMBER.

    In CUBESET you can define range from cubemembers like this "{cubememberexpression1:cubememberexpression2}"

    =CUBEMEMBER("OLAPCUBE","{[Time].[Time].[Year].&[2015].&[1].&[1]:[Time].[Time].[Year].&[2015].&[2].&[2]}")

    But you should do these kind of calculations in your OLAP database. This is very common YTD measure.

    • Marked as answer by Charlie Liao Monday, August 24, 2015 4:51 AM
    Sunday, August 23, 2015 2:36 PM
  • Great - thanks for your help

    Brian


    Brian Hyam

    Sunday, August 23, 2015 10:08 PM
  • Hi

    Have found that while CUBESET works fine for two elements of the same time dimension level I cannot seem to enter more than two elements as cubeset?

    e.g.for three quarters total I entered =CUBESET("OLAPCUBE","{[Time].[Time].[Year].&[2015].&[1]:[Time].[Time].[Year].&[2015].&[2]:[Time].[Time].[Year].&[2015].&[3]}","TEST") replies #N/A

    Also could not seem to mix the dimensions e.g. to add quarters + months?

    e.g =CUBESET("OLAPCUBE","{[Time].[Time].[Year].&[2015].&[1]:[Time].[Time].[Year].&[2015].&[2].&[4]}","TEST") returns #N/A

    Any advice would be appreciated.

    thanks

    Brian


    Brian Hyam

    Monday, August 24, 2015 2:23 PM
  • Hi,

    for multiple elements you can use {(element1),(element2),(element3)}

    for range {element1:element2} or combine these two {(element1),(element2),(element3:element4)}

    I dont think you can mix elements with different attributes.

    Monday, August 24, 2015 3:17 PM
  • Great thanks.

    I have one more question re cubesets....

    I have found that the below formula works as is but if I change it to cubeset using the notation suggested above for a range of time dimensions it stops working. The only difference is the insertion of the 'material classification' condition. I'm guessing its because the kind of grouped expression with the material and time but not sure of the solution?

    =CUBEMEMBER("OLAPCUBE",{"[Material Classification_Resource].[Material Classification_Resource].[Material Classification_Resource].&[{BC261FAE-5CEE-46C8-89B9-9E2197CFDD7B}]","[Time].[Time].[Year].&[2015].&[3].&[7]"},"Cost")     works ok

    =CUBESET("OLAPCUBE",{"[Material Classification_Resource].[Material Classification_Resource].[Material Classification_Resource].&[{BC261FAE-5CEE-46C8-89B9-9E2197CFDD7B}]","[Time].[Time].[Year].&[2015].&[1].&[1]:[Time].[Time].[Year].&[2015].&[3].&[7]"},"Cost")     doesn't work

    Advice appreciated

    Brian


    Brian Hyam

    Tuesday, August 25, 2015 1:28 PM