# Aggregating time periods in Cubemember/Cubevalue formulas

• ### 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 Sunday, August 23, 2015 1:57 PM
Sunday, August 23, 2015 1:56 PM

• 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 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 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

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